package com.budwk.app.sqfw.services.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.text.StrFormatter;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.extra.pinyin.PinyinUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.common.config.AppException;
import com.budwk.app.common.rocketmq.RocketMQServer;
import com.budwk.app.dfxyWechatMP.DfxyWeChatMPUtils;
import com.budwk.app.jim.SqfwWebSocket;
import com.budwk.app.jim.WebSocketMsgDTO;
import com.budwk.app.oss.models.SysOss;
import com.budwk.app.sms.dto.SmsMsgDTO;
import com.budwk.app.sms.service.OpaSmsService;
import com.budwk.app.sms.service.ZtSmsService;
import com.budwk.app.sqfw.bo.PdfResourceSortBO;
import com.budwk.app.sqfw.component.CommonComponent;
import com.budwk.app.sqfw.constants.SqfwFormSummaryConstants;
import com.budwk.app.sqfw.constants.SqfwWebSocketMsgConstants;
import com.budwk.app.sqfw.dto.*;
import com.budwk.app.sqfw.enums.*;
import com.budwk.app.sqfw.models.*;
import com.budwk.app.sqfw.services.*;
import com.budwk.app.sqfw.vo.*;
import com.budwk.app.sqfwV2.service.centuryCity.OldCareServiceStationExcelService;
import com.budwk.app.sys.models.Sys_unit;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysUnitService;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.poiUtils.*;
import com.budwk.app.util.poiUtils.fireHiddenOffice.GeneratePdfConverge;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.exception.BaseException;
import com.budwk.starter.common.page.PageUtil;
import com.budwk.starter.common.page.Pagination;
import com.budwk.starter.common.result.Result;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.database.service.BaseServiceImpl;
import com.budwk.starter.security.utils.SecurityUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.bson.json.JsonObject;
import org.nutz.aop.interceptor.ioc.TransAop;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.impl.PropertiesProxy;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;
import org.nutz.trans.Trans;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;

import static com.budwk.app.util.poiUtils.CommonFormOfficeUtils.createConvergePdfAndUpload;

/**
 * @author LJT
 * @since 2023/10/11
 */
@Slf4j
@IocBean(args = {"refer:dao"})
public class SqfwSummaryForAdminServiceImpl extends BaseServiceImpl<Sqfw_formSummary> implements SqfwSummaryForAdminService {
    public SqfwSummaryForAdminServiceImpl(Dao dao) {
        super(dao);
    }

    public final static String TO_DO_CHECK_OUT = "1";// 对于后台：表示未检查，对于前台：表示超时
    public final static String CHECK_OUT = "2";// 对于后台：表示已检查，  对于前台：表示未超时

    @Inject
    private SysUnitService sysUnitService;

    @Inject
    private SqfwCompleteflowService sqfwCompleteflowService;

    @Inject
    private SqfwWebSocket sqfwWebSocket;

    @Inject
    private SqfwServicerecordService sqfwServicerecordService;

    @Inject
    private OpaSmsService opaSmsService;

    @Inject
    private SysUserService sysUserService;

    @Inject
    private RocketMQServer rocketMQServer;

    @Inject
    private SqfwReportFileService reportFileService;

    @Inject
    private SqfwWorkorderService sqfwWorkorderService;

    @Inject
    private SqfwFormSummaryService sqfwFormSummaryService;

    @Inject
    private SqfwSummaryForAdminService sqfwSummaryForAdminService;

    @Inject
    private PropertiesProxy propertiesProxy;

    @Inject
    private SqfwCenSusPaperService sqfwCenSusPaperService;
    @Inject
    private MeetingMinutesService meetingMinutesService;

    @Inject
    private SqfwSmsMsgService sqfwSmsMsgService;

    @Inject
    private OldCareServiceStationExcelService oldCareServiceStationExcelService;

    @Inject
    private ZtSmsService ztSmsService;

    ExecutorService executorService = Executors.newFixedThreadPool(50);

    @Override
    public List<String> listUnitPlaceName(String unitId, String type, String checkPlaceName, String returnKey) {

        if (StringUtils.isBlank(unitId)) return Lists.newArrayList();

        if (StringUtils.isBlank(returnKey)) returnKey = "a1";

        // 目前只针对场所进行查询
        String sqlStr = " SELECT DISTINCT " + returnKey + " FROM shequ.\"sqfw_form_summary\" where unit_id = @unitId ";

        if (StringUtils.isNotBlank(checkPlaceName)) sqlStr += " and @returnKey like @unitName ";
        if (StringUtils.isNotBlank(type)) sqlStr += " and type = @type ";

        Sql sql = Sqls.create(sqlStr);
        sql.setParam("returnKey", returnKey);
        sql.setParam("unitId", unitId);

        if (StringUtils.isNotBlank(checkPlaceName)) sql.setParam("unitName", "%" + checkPlaceName + "%");
        if (StringUtils.isNotBlank(type)) sql.setParam("type", type);

        String finalReturnKey = returnKey;
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<String> list = Lists.newArrayList();
            while (rs.next())
                list.add(rs.getString(finalReturnKey));
            return list;
        });
        dao().execute(sql);
        return sql.getList(String.class);
    }

    @Override
    public List<JsonObject> listUnitPlaceInfoMulti(String unitId, String type, String checkPlaceName, String returnKey) {
        if (StringUtils.isBlank(unitId)) return Lists.newArrayList();

        if (StringUtils.isBlank(returnKey)) returnKey = "a1";

        // 目前只针对场所进行查询
        String sqlStr = " SELECT DISTINCT " + returnKey + " FROM shequ.\"sqfw_form_summary\" where unit_id = @unitId ";

        if (StringUtils.isNotBlank(checkPlaceName)) sqlStr += " and @returnKey like @unitName ";
        if (StringUtils.isNotBlank(type)) sqlStr += " and type = @type ";

        Sql sql = Sqls.create(sqlStr);
        sql.setParam("returnKey", returnKey);
        sql.setParam("unitId", unitId);

        if (StringUtils.isNotBlank(checkPlaceName)) sql.setParam("unitName", "%" + checkPlaceName + "%");
        if (StringUtils.isNotBlank(type)) sql.setParam("type", type);

        String finalReturnKey = returnKey;
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<Map<String, Object>> list = Lists.newArrayList();
            while (rs.next()) {
                Map<String, Object> paramMap = Maps.newHashMap();
                String[] fieldNameList = finalReturnKey.split(",");
                for (String fieldName : fieldNameList) {
                    Object valueObj = rs.getObject(fieldName);
                    paramMap.put(fieldName, valueObj);
                }
                list.add(paramMap);
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(JsonObject.class);
    }

    @Override
    public SqfwFormSummaryUnitInfoVO getUnitPlaceLatestInfo(String unitNameStr, String unitId) {
        if (StringUtils.isBlank(unitId)) return null;
        Cnd cnd = Cnd.where("a1", "=", unitNameStr).and("unit_id", "=", unitId);
        cnd.orderBy("fill_date", "desc");
        Sqfw_formSummary formSummary = fetch(cnd);
        SqfwFormSummaryUnitInfoVO unitInfoVO = new SqfwFormSummaryUnitInfoVO();
        BeanUtil.copyProperties(formSummary, unitInfoVO);
        return unitInfoVO;
    }

    @Override
    public SqfwFormSummaryUnitInfoVO getUnitPlaceLatestInfoById(String id) {
        Sqfw_formSummary formSummary = fetch(id);
        SqfwFormSummaryUnitInfoVO unitInfoVO = new SqfwFormSummaryUnitInfoVO();
        BeanUtil.copyProperties(formSummary, unitInfoVO);
        return unitInfoVO;
    }

    private void doGenerateFireHiddenPdf(Sqfw_completeflow completeFlow, Sqfw_formSummary formSummary, boolean sendMsgFlag) {
        boolean successFlag = false;
        String resourcePath = "";
        String sendMsgPhone = "";

        try {
            String unitId = formSummary.getUnitId();
            Sys_unit unit = sysUnitService.fetch(unitId);

            FireHiddenCheckWordDTO wordDTO = new FireHiddenCheckWordDTO();
            BeanUtil.copyProperties(formSummary, wordDTO);
            wordDTO.setCompleteUnitName(unit.getName());

            GenerateWordAndPdfUrlVO wordVO = createFireHiddenCheckFormWordAndPDFByUnitName(wordDTO);
            if (StringUtils.isNotBlank(wordVO.getPdfUrl())) {
                resourcePath = wordVO.getPdfUrl();
                sendMsgPhone = formSummary.getA8();
                successFlag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 生成完成之后更新工作流状态
        if (successFlag) completeFlow.setResourcePath(resourcePath);
        else completeFlow.setResourcePath("error");
        sqfwCompleteflowService.update(completeFlow);

        // 当生成完毕之后就直接发送webSocket消息或者短信消息
        sendMsgAfterFireHiddenPdfOrExcel(completeFlow.getUserId(), sendMsgPhone, sendMsgFlag);
    }

    @Override
    public void retryGenerateFireHiddenPdf(String userId, String sqfwFlowId) {
        // 首先根据工作流id查询到表单的数据
        Sqfw_completeflow sqfwCompleteFlow = sqfwCompleteflowService.fetch(sqfwFlowId);
        if (sqfwCompleteFlow == null) return;
        // 将工作流的pdf生成状态设置为creating
        sqfwCompleteFlow.setResourcePath("creating");
        sqfwCompleteflowService.update(sqfwCompleteFlow);

        // 然后组成相应的DTO 创建pdf
        Cnd cnd = Cnd.NEW().and("sqfw_flow_id", "=", sqfwFlowId);
        Sqfw_formSummary formSummaryOfFlow = fetch(cnd);

        executorService.execute(() -> {
            doGenerateFireHiddenPdf(sqfwCompleteFlow, formSummaryOfFlow, false);
        });
    }

    /**
     * 根据服务记录id生成消防安全excel数据
     *
     * @param sqfwId      服务记录id
     * @param workOrderId 工单id
     * @param userId      用户id
     * @param fileIndex   文件索引
     * @return excel文件链接
     */
    @Override
    public String generateFireHiddenCollectionExcelForApp(String downType, String sqfwId, String workOrderId, String userId, Integer fileIndex) {
        if (StringUtils.isBlank(sqfwId)) return null;
        return generateFireHiddenCollectionExcelForApp(downType, Lists.newArrayList(sqfwId), workOrderId, userId, fileIndex, null);
    }

    private String generateFireHiddenCollectionExcelForApp(String downType, List<String> sumIdList, String workOrderId, String userId, Integer fileIndex, String fileNameSuffix) {
        if (sumIdList.size() == 0) {
            return null;
        }
        Cnd cnd = Cnd.NEW();
        cnd.and("id", "in", sumIdList);

        if (StringUtils.isNotBlank(workOrderId)) {
            cnd.and("work_order_id", "=", workOrderId);
        }

        cnd.orderBy("fill_date", "asc");

        List<Sqfw_formSummary> formSummaryList = query(cnd);
        if (formSummaryList.isEmpty()) {
            throw new AppException("当前数据不存在");
        }

        String sqfwId = formSummaryList.get(0).getSqfwId();
        Sqfw_servicerecord serviceRecord = sqfwServicerecordService.fetch(sqfwId);

        AbstractExcelParamDTO excelParamDTO = new AbstractExcelParamDTO();
        if ("zip".equals(downType)) {
            excelParamDTO.setReturnWebUrlFlag(false);
        }
        excelParamDTO.setFillDate(new Date());
        excelParamDTO.setSignalPersonImgUrl("");
        excelParamDTO.setUnitNamePrefix(serviceRecord.getCompanyName());
        excelParamDTO.setCompleteUnitName(serviceRecord.getCounty());
        excelParamDTO.setFormSummaryList(formSummaryList);

        String unitName = "";
        try {
            Sys_user createServiceRecordUser = sysUserService.getUserById(serviceRecord.getUser_id());
            if (StringUtils.isNotEmpty(serviceRecord.getUser_id())) {
                String unitId = createServiceRecordUser.getUnitId();
                Sys_unit unit = sysUnitService.getMasterCompany(unitId);
                unitName = unit.getName();
            }
        } catch (BaseException bex) {
            log.info("查询异常：{}", bex.getMessage());
            unitName = formSummaryList.get(0).getCounty();
        } finally {
            if (StringUtils.isEmpty(unitName)) {
                throw new BaseException("用户不存在");
            }
        }

        String titleUnitName = serviceRecord.getCounty();
        if (Strings.isBlank(titleUnitName)) {
            titleUnitName = unitName;
        }
        if (!titleUnitName.contains("街道")) {
            titleUnitName += "街道";
        }
        if (Strings.isNotBlank(titleUnitName)) {
            titleUnitName = titleUnitName.substring(0, titleUnitName.lastIndexOf("街道") + 2);
        }
        // 油榨街街道的
        if (unitName.contains("油榨街")) {
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            return FireHiddenOfficeUtils.createFireHiddenExcelYouZhaJieShiNanSQ(excelParamDTO, null);
        }

        // 金元社区的
        if (unitName.contains("金元") || unitName.contains("润兴") || unitName.contains("金阳")) {
            Sys_user createFileUser = sysUserService.fetch(userId);

            FireHiddenExcelParamJinYuanSQDTO dto = new FireHiddenExcelParamJinYuanSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);

            if (unitName.contains("金元")) {
                dto.setVisAndInsExcelTitlePrefix(StrFormatter.format("金阳街道金元社区九小场所{}", DateUtil.format(dto.getFillDate(), "yyyy年MM月")));
                dto.setGasInsExcelTitlePrefix("金阳街道金元社区");
                dto.setGasRecExcelTitlePrefix("观山湖区金元社区");
                dto.setFireSafetyExcelTitlePrefix("金阳街道金元社区");
            }

            if (unitName.contains("润兴")) {
                dto.setVisAndInsExcelTitlePrefix(StrFormatter.format("金阳街道润兴居委会{}", DateUtil.format(dto.getFillDate(), "yyyy年MM月")));
                dto.setGasInsExcelTitlePrefix("金阳街道润兴居委会");
                dto.setGasRecExcelTitlePrefix("观山湖区润兴居委会");
                dto.setFireSafetyExcelTitlePrefix("金阳街道润兴居委会");
            }

            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setCompleteUnitName(titleUnitName);

            if (fileIndex == null) fileIndex = 1;
            dto.setFileIndex(fileIndex);

            // 如果是生成台账，则需要查询一些数据用来进行填装
            if (fileIndex == 0 || fileIndex == 4) {
                List<Sqfw_formSummary> jinYuanFormSummaryList = dto.getFormSummaryList();

                Map<String, Sqfw_formSummary> latestA1AndA13Map = Maps.newHashMap();
                for (Sqfw_formSummary jinYuanFormSummary : jinYuanFormSummaryList) {
                    String jinYuanA1 = jinYuanFormSummary.getA1();
                    String jinYuanA13 = jinYuanFormSummary.getA13();

                    if (jinYuanA13.equals("整改完成")) {
                        Cnd jinYuanCnd = Cnd.NEW();
                        jinYuanCnd.and("a1", "=", jinYuanA1).and("a13", "=", "有");
                        jinYuanCnd.orderBy("fill_date", "desc");
                        Sqfw_formSummary jinYuanQueryFormSummary = fetch(jinYuanCnd);
                        if (jinYuanQueryFormSummary != null && !jinYuanQueryFormSummary.getId().equals(jinYuanFormSummary.getId()))
                            latestA1AndA13Map.put(jinYuanQueryFormSummary.getA1(), jinYuanQueryFormSummary);
                    }

                }
                dto.setLatestA1AndA13Map(latestA1AndA13Map);
            }

            // 生成文件
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            VisAndInsForJinYangJieDaoExcelVO jinYuanFireHiddenExcelVO = FireHiddenOfficeUtils.createFireHiddenExcelJinYuanSQ(dto, null);
            if (jinYuanFireHiddenExcelVO.getErrorStatus()) return null;

            if (fileIndex == 1) return jinYuanFireHiddenExcelVO.getVisAndInsExcelUrl();
            if (fileIndex == 2) return jinYuanFireHiddenExcelVO.getGasInsExcelUrl();
            if (fileIndex == 3) return jinYuanFireHiddenExcelVO.getGasRecExcelUrl();
            if (fileIndex == 4) return jinYuanFireHiddenExcelVO.getStandingBookExcelUrl();

            if (fileIndex == 0)
                return StrFormatter.format("{},{},{},{}", jinYuanFireHiddenExcelVO.getVisAndInsExcelUrl(),
                        jinYuanFireHiddenExcelVO.getGasInsExcelUrl(), jinYuanFireHiddenExcelVO.getGasRecExcelUrl(), jinYuanFireHiddenExcelVO.getStandingBookExcelUrl());
        }

        //大营路街道
        if (unitName.contains("大营路")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamDaYingLuSQDTO dto = new FireHiddenExcelParamDaYingLuSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setStreet(createFileUser.getCounty());
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            dto.setArea(serviceRecord.getDistrict());
            dto.setStreet(serviceRecord.getCounty());
            dto.setDataSource(CommonFormOfficeForAdminUtils.APPLICATION_ADMIN);

            List<Sqfw_formSummary> preFormSummaryList = dto.getFormSummaryList();
            Map<String, Sqfw_formSummary> lastFormSummaryCheckMap = Maps.newHashMap();
            for (Sqfw_formSummary formSummary : preFormSummaryList) {
                Cnd dylLastCheckCND = Cnd.NEW();
                dylLastCheckCND.and(Sqfw_formSummary::getA1, "=", formSummary.getA1())
                        .and(Sqfw_formSummary::getUnitId, "=", formSummary.getUnitId());
                dylLastCheckCND.orderBy(Sqfw_formSummary::getFillDate, "desc");
                Sqfw_formSummary lastCheckFormSummary = fetch(dylLastCheckCND);
                if (lastCheckFormSummary != null && !lastCheckFormSummary.getId().equals(formSummary.getId())) {
                    lastFormSummaryCheckMap.put(formSummary.getId(), lastCheckFormSummary);
                }
            }
            dto.setLastFormSummaryCheckMap(lastFormSummaryCheckMap);
            return DaYingLuStreetFireHiddenExcelUtils.createFireHiddenExcelDaYingLuSQ(dto, null);
        }

        if (unitName.contains("威清门")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamWeiQingMenSQDTO dto = new FireHiddenExcelParamWeiQingMenSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setProvince(createFileUser.getProvince());
            dto.setCity(createFileUser.getCity());
            dto.setDistrict(createFileUser.getDistrict());
            dto.setCounty(createFileUser.getCounty());
            //dto.setCommunity(createFileUser.getCommunity());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            return WeiQingMenStreetFireHiddenExcelUtils.createFireHiddenExcelSQ(dto, null);
        }
        //宇虹社区
        if (unitName.contains("云潭街道")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamYuHongSQDTO dto = new FireHiddenExcelParamYuHongSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setCounty(createFileUser.getCounty());
            //dto.setCommunity(createFileUser.getCommunity());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            return YuHongMenStreetFireHiddenExcelUtils.createExcelSQ(dto, null);
        }
        // 其他社区的
        return FireHiddenOfficeUtils.createFireHiddenExcel(excelParamDTO, null);

    }

    @Override
    public Integer sendFireHiddenPeersMsg(FireHiddenPeersMsgDTO dto) {

        String fromUserName = dto.getFromUserName();
        String toUserPhone = dto.getToUserPhone();
        if (StringUtils.isBlank(toUserPhone) || StringUtils.isBlank(fromUserName)) return 0;

        String msgContent = StrFormatter.format("请知悉：{} {}在{}任务中，检查{}时，将你添加为同行人",
                DateUtil.format(new Date(), "yyyy-MM-dd HH:mm:ss"), fromUserName, dto.getServiceType(), dto.getA1());
        SmsMsgDTO smsMsgDTO = new SmsMsgDTO();
        smsMsgDTO.setPhoneNumberSet(Sets.newHashSet(toUserPhone.split(",")));
        smsMsgDTO.setTemplateParamList(Lists.newArrayList(msgContent));
        return opaSmsService.opaSmsSendOneMessage(smsMsgDTO);
    }

    @Override
    public String generateFormSummaryPdfConverge(String sqfwId, String workOrderId, String fileType) {
        if (StringUtils.isBlank(sqfwId)) return null;

        Cnd cnd = Cnd.NEW();
        cnd.and("sqfw_id", "=", sqfwId);

        if (StringUtils.isBlank(fileType)) fileType = SqfwFormSummaryConstants.safety_check;
        cnd.and("file_type", "=", fileType);

        if (StringUtils.isNotBlank(workOrderId))
            cnd.and("worker_id", "=", workOrderId);

        cnd.orderBy("create_date", "asc");
        List<Sqfw_completeflow> completeFlowList = sqfwCompleteflowService.query(cnd);
        if (completeFlowList.size() == 0) return null;

        List<String> pdfList = completeFlowList.stream().map(Sqfw_completeflow::getResourcePath).collect(Collectors.toList());

        try {
            String fileName = "";
            if (StringUtils.isNotBlank(fileType)) {
                fileName = "pdf" + fileType + "整治表集合";
            }
            return createConvergePdfAndUpload(pdfList, fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public void generateRefuseClassifyPdf(String userId, SqfwFormSummaryWithFlowDTO formSummaryWithFlow) {

        insertWordFlowAndFormSummary(userId, formSummaryWithFlow, SqfwFormSummaryConstants.refuse_classify);

        Sqfw_completeflow completeFlow = formSummaryWithFlow.getCompleteFlow();
        Sqfw_formSummary formSummary = formSummaryWithFlow.getFormSummary();

        executorService.execute(() -> generateRefuseClassifyPDFByFlowAndForm(userId, completeFlow, formSummary));
    }

    @Override
    public String generateRefuseClassifyExcelForApp(String sqfwId) {
        if (StringUtils.isBlank(sqfwId)) return null;
        return generateRefuseClassifyExcelForApp("", Lists.newArrayList(sqfwId), null);
    }

    private String generateRefuseClassifyExcelForApp(String downType, List<String> sumIdList, String fileNameSuffix) {
        if (sumIdList.size() == 0) {
            return null;
        }
        Cnd cnd = Cnd.NEW();
        cnd.and("id", "in", sumIdList).and("type", "=", SqfwFormSummaryConstants.refuse_classify);
        cnd.orderBy("fill_date", "asc");
        List<Sqfw_formSummary> formSummaryList = query(cnd);
        if (formSummaryList.isEmpty()) {
            throw new AppException("当前数据不存在");
        }
        String sqfwId = formSummaryList.get(0).getSqfwId();
        Sqfw_servicerecord serviceRecord = sqfwServicerecordService.fetch(sqfwId);

        RefuseClassifyExcelParamDTO excelParamDTO = new RefuseClassifyExcelParamDTO();
        if ("zip".equals(downType)) {
            excelParamDTO.setReturnWebUrlFlag(false);
        }
        excelParamDTO.setFillDate(new Date());
        excelParamDTO.setSignalPersonImgUrl("");
        excelParamDTO.setAreaLevel(serviceRecord.getCounty() + serviceRecord.getCommunity());
        excelParamDTO.setCompleteUnitName(serviceRecord.getCompanyName());

        excelParamDTO.setFormSummaryList(formSummaryList);
        try {
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            return RefuseClassifyOfficeUtils.createRefuseClassifyExcel(excelParamDTO, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void retryGenerateRefuseClassifyPdf(String userId, String sqfwFlowId) {
        // 首先根据工作流id查询到表单的数据
        Sqfw_completeflow sqfwCompleteFlow = sqfwCompleteflowService.fetch(sqfwFlowId);
        if (sqfwCompleteFlow == null) return;
        String resourcePath = sqfwCompleteFlow.getResourcePath();
        if (resourcePath.contains("/")) return;  // 说明之前是创建成功的
        // 将工作流的pdf生成状态设置为creating
        sqfwCompleteFlow.setResourcePath("creating");
        sqfwCompleteflowService.update(sqfwCompleteFlow);

        // 然后组成相应的DTO 创建pdf
        Cnd cnd = Cnd.NEW().and("sqfw_flow_id", "=", sqfwFlowId);
        Sqfw_formSummary formSummaryOfFlow = fetch(cnd);

        executorService.execute(() -> generateRefuseClassifyPDFByFlowAndForm(userId, sqfwCompleteFlow, formSummaryOfFlow));
    }


    // ------------------------------------------------------------------------------
    //  生成入户调查表数据
    // ------------------------------------------------------------------------------

    /**
     * 根据户号和栋号查询信息
     *
     * @param a5 户号
     * @param a4 栋号
     * @return 结果
     */
    @Override
    public List<HouseSurveyFormPlaceVO> listPlaceForHouseSurveyFrom(String unitId, String workOrderId, String userId, String a5, String a4) {

        List<HouseSurveyFormPlaceVO> resultList = Lists.newArrayList();

        if (StringUtils.isBlank(unitId)) return resultList;
        if (StringUtils.isBlank(a5) && StringUtils.isBlank(a4)) return resultList;

        Cnd cnd = Cnd.NEW();
        cnd.and("unit_id", "=", unitId).and("type", "=", SqfwFormSummaryConstants.house_survey_form)
                .and("create_by", "=", userId).and("work_order_id", "=", workOrderId);
        cnd.orderBy("fill_date", "desc");
        cnd.limit(1, 1);
        Sqfw_formSummary tempLastSurveyForm = fetch(cnd);

        Sqfw_formSummary lastSurveyForm = null;
        if (tempLastSurveyForm != null) {
            Cnd lastSurveyCnd = Cnd.NEW();
            lastSurveyCnd.and("unit_id", "=", unitId).and("type", "=", SqfwFormSummaryConstants.house_survey_form);
            lastSurveyCnd.and("a1", "=", tempLastSurveyForm.getA1());
            if (tempLastSurveyForm.getA4() != null) lastSurveyCnd.and("a4", "=", tempLastSurveyForm.getA4());
            lastSurveyCnd.and("a5", "like", StrFormatter.format("%{}%", a5));
            lastSurveyForm = fetch(lastSurveyCnd);
        }

        // 用于从第二部分查询中去除第一部分存在的数据
        String lastSurveyConditionKey = "";
        if (lastSurveyForm != null)
            lastSurveyConditionKey = StrFormatter.format("{},{},{}", lastSurveyForm.getA1(), lastSurveyForm.getA4(), lastSurveyForm.getA5());

        // 这个方法的逻辑就是根据a5查到上一次这个人填写的a5的信息，然后其余的就按照校区院落的字母 栋数 门牌号大小排序
        Cnd otherCnd = Cnd.NEW();
        otherCnd.and("unit_id", "=", unitId).and("type", "=", SqfwFormSummaryConstants.house_survey_form);
        otherCnd.and("a5", "like", StrFormatter.format("%{}%", a5));
        if (StringUtils.isNotBlank(a4)) otherCnd.and("a4", "=", a4);
        otherCnd.orderBy("fill_date", "desc");
        List<Sqfw_formSummary> otherSurveyFormList = query(otherCnd);
        Map<String, Sqfw_formSummary> filterMap = Maps.newHashMap();
        for (Sqfw_formSummary formSummary : otherSurveyFormList) {
            String conditionKey = StrFormatter.format("{},{},{}", formSummary.getA1(), formSummary.getA4(), formSummary.getA5());
            if (filterMap.containsKey(conditionKey)) continue;

            filterMap.put(conditionKey, formSummary);
        }

        List<HouseSurveyFormPlaceVO> tempResultList = Lists.newArrayList();
        List<HouseSurveyFormPlaceVO> noA4List = Lists.newArrayList();

        for (String conditionKey : filterMap.keySet()) {
            Sqfw_formSummary formSummary = filterMap.get(conditionKey);

            if (lastSurveyForm != null && conditionKey.equals(lastSurveyConditionKey)) continue;

            HouseSurveyFormPlaceVO placeVO = new HouseSurveyFormPlaceVO();
            BeanUtil.copyProperties(formSummary, placeVO);
            if (StringUtils.isNotBlank(formSummary.getA1())) {
                placeVO.setA1Char(PinyinUtil.getFirstLetter(formSummary.getA1(), ""));
            } else {
                placeVO.setA1Char("");
            }

            if (StringUtils.isBlank(placeVO.getA4())) noA4List.add(placeVO);
            else tempResultList.add(placeVO);
        }

        // 首先按照a1排序  然后按照a4排序 然后按照a5排序
        tempResultList = tempResultList.stream().sorted(
                Comparator.comparing(HouseSurveyFormPlaceVO::getA1Char)
                        .thenComparing(HouseSurveyFormPlaceVO::getA4)
                        .thenComparing(HouseSurveyFormPlaceVO::getA5)
        ).collect(Collectors.toList());

        noA4List = noA4List.stream().sorted(
                Comparator.comparing(HouseSurveyFormPlaceVO::getA1Char)
                        .thenComparing(HouseSurveyFormPlaceVO::getA5)
        ).collect(Collectors.toList());

        if (lastSurveyForm != null) {
            HouseSurveyFormPlaceVO lastPlaceVO = new HouseSurveyFormPlaceVO();
            BeanUtil.copyProperties(lastSurveyForm, lastPlaceVO);
            if (StringUtils.isNotBlank(lastSurveyForm.getA1())) {
                lastPlaceVO.setA1Char(PinyinUtil.getFirstLetter(lastSurveyForm.getA1(), ""));
            } else {
                lastPlaceVO.setA1Char("");
            }
            resultList.add(lastPlaceVO);
        }

        resultList.addAll(tempResultList);
        resultList.addAll(noA4List);
        return resultList;
    }

    /**
     * 生成入户调查表数据
     *
     * @param userId              用户id
     * @param formSummaryWithFlow 表单数据
     */
    @Override
    public void generateHouseSurveyFormPdf(String userId, SqfwFormSummaryWithFlowDTO formSummaryWithFlow) {
        insertWordFlowAndFormSummary(userId, formSummaryWithFlow, SqfwFormSummaryConstants.house_survey_form);

        Sqfw_completeflow completeFlow = formSummaryWithFlow.getCompleteFlow();
        Sqfw_formSummary formSummary = formSummaryWithFlow.getFormSummary();

        executorService.execute(() -> generateHouseSurveyFormPDFByFlowAndForm(userId, completeFlow, formSummary));
    }

    @Override
    public List<String> getDownloadHouseSurveyFormExcelType(String sqfwId) {
        Cnd cnd = Cnd.NEW();
        cnd.and("sqfw_id", "=", sqfwId).and("type", "=", SqfwFormSummaryConstants.house_survey_form);
        List<Sqfw_formSummary> formSummaryList = query(cnd);

        List<String> resultList = Lists.newArrayList();
        if (formSummaryList.size() == 0) return resultList;

        Set<String> typeSet = Sets.newHashSet();
        for (Sqfw_formSummary formSummary : formSummaryList) {
            String a16 = formSummary.getA16();
            Set<String> a16Set = Sets.newHashSet(a16.split(","));
            typeSet.addAll(a16Set);
        }
        return Lists.newArrayList(typeSet);
    }

    /**
     * 按照类型生成入户调查表excel
     *
     * @param sqfwId    社区服务id
     * @param excelType excel类型
     * @return excel链接
     */
    /**
     * 按照类型生成入户调查表excel
     *
     * @param excelType excel类型
     * @return excel链接
     */
    public String generateHouseSurveyFormExcelForAdmin(String downType, List<String> sumIdList, String excelType, String fileNameSuffix) {
        if (Strings.isBlank(fileNameSuffix)) {
            //fileNameSuffix = StrFormatter.format("集合{}", DateUtil.format(new Date(), "yyyyMMddHHmmss"));
            fileNameSuffix = DateUtil.format(new Date(), "yyyyMMddHHmmss");
        }
        return generateHouseSurveyFormExcelForApp(downType, sumIdList, excelType, fileNameSuffix);
    }

    private String generateHouseSurveyFormExcelForApp(String downType, List<String> sumIdList, String excelType, String fileNameSuffix) {
        if (sumIdList.size() == 0) {
            return null;
        }
/*        // wzy 2023-10-11 临时处理垃圾分类改为垃圾分类宣传
        String tempExcelType = excelType;
        if (tempExcelType.equals("垃圾分类")) tempExcelType = "垃圾分类宣传";*/
        Cnd cnd = Cnd.NEW();
        cnd.and("id", "in", sumIdList).and("type", "=", SqfwFormSummaryConstants.house_survey_form);
        cnd.orderBy("fill_date", "asc");
        List<Sqfw_formSummary> formSummaryList = query(cnd);
        return CommonComponent.generateHouseSurveyFormExcelForApp(downType, formSummaryList, excelType, fileNameSuffix, null, null, null);
    }

    /**
     * 重新生成入户调查表PDF
     *
     * @param userId     用户id
     * @param sqfwFlowId 工作流id
     */
    @Override
    public void retryGenerateHouseSurveyFormPdf(String userId, String sqfwFlowId) {
        // 首先根据工作流id查询到表单的数据
        Sqfw_completeflow sqfwCompleteFlow = sqfwCompleteflowService.fetch(sqfwFlowId);
        if (sqfwCompleteFlow == null) return;
        String resourcePath = sqfwCompleteFlow.getResourcePath();
        if (resourcePath.contains("/")) return;  // 说明之前是创建成功的
        // 将工作流的pdf生成状态设置为creating
        sqfwCompleteFlow.setResourcePath("creating");
        sqfwCompleteflowService.update(sqfwCompleteFlow);

        // 然后组成相应的DTO 创建pdf
        Cnd cnd = Cnd.NEW().and("sqfw_flow_id", "=", sqfwFlowId);
        Sqfw_formSummary formSummaryOfFlow = fetch(cnd);

        executorService.execute(() -> generateHouseSurveyFormPDFByFlowAndForm(userId, sqfwCompleteFlow, formSummaryOfFlow));
    }

    @Override
    public List<HouseSurveyFormPlaceVO> listSecondHouseSurvey(String sqfwId, String userId) {
        Cnd cnd = Cnd.NEW();
        cnd.and("sqfw_id", "=", sqfwId)
                .and("create_by", "=", userId)
                .and("type", "=", SqfwFormSummaryConstants.house_survey_form);
        cnd.orderBy("fill_date", "desc");
        List<Sqfw_formSummary> queryList = query(cnd);

        List<HouseSurveyFormPlaceVO> resultList = Lists.newArrayList();
        List<HouseSurveyFormPlaceVO> noA4List = Lists.newArrayList();
        Set<String> existedPlaceSet = Sets.newHashSet();
        for (Sqfw_formSummary formSummary : queryList) {

            String placeKey = StrFormatter.format("{},{},{}", formSummary.getA1(), formSummary.getA4(), formSummary.getA5());

            Set<String> a17ConditionSet = Sets.newHashSet("自住", "出租");
            Set<String> a20ConditionSet = Sets.newHashSet("无人", "其他");
            String a17 = formSummary.getA17();
            String a20 = formSummary.getA20();
            if (!a17ConditionSet.contains(a17) || !a20ConditionSet.contains(a20)) {
                existedPlaceSet.add(placeKey);
                continue;
            }

            if (!existedPlaceSet.contains(placeKey)) {
                existedPlaceSet.add(placeKey);
                HouseSurveyFormPlaceVO vo = new HouseSurveyFormPlaceVO();
                BeanUtil.copyProperties(formSummary, vo);
                if (StringUtils.isNotBlank(vo.getA1())) {
                    vo.setA1Char(PinyinUtil.getFirstLetter(vo.getA1(), ""));
                } else {
                    vo.setA1Char("");
                }

                if (StringUtils.isBlank(formSummary.getA4())) noA4List.add(vo);
                else resultList.add(vo);
            }
        }

        resultList = sortForHouseSurveyByPlace(resultList, noA4List);
        return resultList;
    }

    @Override
    public String generateServiceRecordPdfColl(List<SqfwSummaryPdfExcelCollForAdminDTO> conditionList) {

        // 目前pdf存储于两个地方，一个是安全排查、垃圾分类、入户调查等业务的pdf存储在工作流中的sqfw_complete_flow的resource中
        // 另外的 12345、八单一表、简报、日报、其他任务都是存储于fileReport这张表中。
        List<PdfResourceSortBO> pdfBoList = Lists.newArrayList();

        Set<String> flowTypeSet = Sets.newHashSet("垃圾分类", "安全排查", "入户调查", SupportBusinessTypeEnum.MEET_RECORD.getName(), SupportBusinessTypeEnum.PUBLIC_SECURITY.getName());
        Set<String> fileReportTypeSet = Sets.newHashSet("12345", "简报", "八单一表", "日报", "其他");

        Map<String, SqfwSummaryPdfExcelCollForAdminDTO> fromFlowDTOMap = Maps.newHashMap();
        Map<String, SqfwSummaryPdfExcelCollForAdminDTO> fromFileReportMap = Maps.newHashMap();
        for (SqfwSummaryPdfExcelCollForAdminDTO sqfwSummaryPdfExcelCollForAdminDTO : conditionList) {
            String serviceType = sqfwSummaryPdfExcelCollForAdminDTO.getServiceType();
            if (flowTypeSet.contains(serviceType))
                fromFlowDTOMap.put(sqfwSummaryPdfExcelCollForAdminDTO.getSumId(), sqfwSummaryPdfExcelCollForAdminDTO);
            if (fileReportTypeSet.contains(serviceType))
                fromFileReportMap.put(sqfwSummaryPdfExcelCollForAdminDTO.getSumId(), sqfwSummaryPdfExcelCollForAdminDTO);
        }

        // 首先查询存储于sqfw_complete_flow中的数据
        if (fromFlowDTOMap.size() > 0) {
            Cnd cnd = Cnd.NEW();
            cnd.and("id", "in", fromFlowDTOMap.keySet());
            cnd.orderBy("fill_date", "asc");
            List<Sqfw_formSummary> formSummaryList = query(cnd);

            Map<String, Date> completeFlowIdDateMap = formSummaryList.stream()
                    .collect(Collectors.toMap(Sqfw_formSummary::getSqfwFlowId, Sqfw_formSummary::getFillDate));

            List<String> flowIdList = formSummaryList.stream().map(Sqfw_formSummary::getSqfwFlowId).collect(Collectors.toList());

            Cnd flowCnd = Cnd.NEW();
            flowCnd.and("id", "in", flowIdList);
            List<Sqfw_completeflow> completeFlowList = sqfwCompleteflowService.query(flowCnd);
            for (Sqfw_completeflow completeFlow : completeFlowList) {
                PdfResourceSortBO bo = new PdfResourceSortBO();
                Date fillDate = completeFlowIdDateMap.get(completeFlow.getId());
                bo.setSortDate(fillDate == null ? new Date() : fillDate);
                bo.setSqfwId(completeFlow.getSqfwId());
                bo.setPdfPath(completeFlow.getResourcePath());
                bo.setServiceType(completeFlow.getFileType());
                pdfBoList.add(bo);
            }
        }

        // 然后从file_report中根据服务id查询出相关的数据 这一步我们使用循环查询的方式来操作
        if (fromFileReportMap.size() > 0) {
            Set<String> sqfwIdSet = fromFileReportMap.keySet();
            for (String sqfwId : sqfwIdSet) {

                Set<String> existedReportNameSet = Sets.newHashSet();

                Cnd fileReportCnd = Cnd.NEW();
                fileReportCnd.and("sqfw_id", "=", sqfwId).orderBy("create_date", "desc");
                List<Sqfw_reportFile> reportFileList = reportFileService.query(fileReportCnd);
                for (Sqfw_reportFile reportFile : reportFileList) {
                    String reportName = reportFile.getReportName();
                    if (StringUtils.isNotBlank(reportName) && !existedReportNameSet.contains(reportName)) {
                        existedReportNameSet.add(reportName);

                        PdfResourceSortBO bo = new PdfResourceSortBO();
                        bo.setSortDate(reportFile.getCreateDate());
                        bo.setSqfwId(reportFile.getSqfwId());
                        bo.setPdfPath(reportFile.getPdfFileUrl());
                        bo.setServiceType(reportFile.getServiceRecordType());
                        pdfBoList.add(bo);
                    }
                }
            }
        }

        // 将这些文档按照时间先后顺序进行排序
        pdfBoList.sort(Comparator.comparing(PdfResourceSortBO::getSortDate));

        List<String> pdfPathList = pdfBoList.stream().map(PdfResourceSortBO::getPdfPath).collect(Collectors.toList());

        // 用于生成文件规则
        Set<String> serviceTypeTotalSet = conditionList.stream().map(SqfwSummaryPdfExcelCollForAdminDTO::getServiceType).collect(Collectors.toSet());
        //String fileNameTemplate = "{}文档集合{}";
        //zhangp 2023-12-13 据产品要求文件名统一修改
        String fileNameTemplate = "{}_{}";

        String fileNamePrefix = "工作文档";
        //if (serviceTypeTotalSet.size() == 1) fileNamePrefix = serviceTypeTotalSet.iterator().next();
        if (serviceTypeTotalSet.size() == 1) {
            fileNamePrefix = serviceTypeTotalSet.iterator().next();
            if (StringUtils.isNotBlank(fileNamePrefix)) {
                String fileName = DownLoadFileNameEnum.getNameVagueByName(fileNamePrefix);
                if (SupportBusinessTypeEnum.HOUSE_SURVEY_FORM.getName().contains(fileNamePrefix)) {
                    fileName = DownLoadFileNameEnum.HOME_REGISTRATION_PDF.getName();
                } else if (SupportBusinessTypeEnum.RUBBISH_CLASSIFY.getName().contains(fileNamePrefix)) {
                    fileName = DownLoadFileNameEnum.RUBBISH_CLASS_PDF.getName();
                }
                if (StringUtils.isNotBlank(fileName)) fileNamePrefix = fileName;
            }

        }
        String fileName = StrFormatter.format(fileNameTemplate, fileNamePrefix, DateUtil.format(new Date(), "yyyyMMddHHmmss"));
        String pdfUrl = CommonFormOfficeUtils.createConvergePdfAndUpload(pdfPathList, fileName);
        if (Strings.isNotBlank(pdfUrl) && pdfUrl.startsWith("http")) {
            pdfUrl = pdfUrl.replace(NutzIocUtils.getOssPrefix(), "");
            pdfUrl = pdfUrl.replace("https://dfsj-standard.obs.cn-south-1.myhuaweicloud.com:443", "");
        }
        return pdfUrl;
    }

    @Override
    public String generateCheckRecordExcelColl(String downType, List<SqfwSummaryPdfExcelCollForAdminDTO> conditionList, String userId, Integer fileIndex, String excelType) {
        if (conditionList.size() == 0) return null;

        Set<String> serviceTypeSet = conditionList.stream().map(SqfwSummaryPdfExcelCollForAdminDTO::getServiceType).collect(Collectors.toSet());
        if (serviceTypeSet.size() > 1) return "任务类型不同，无法合并";

        List<String> sumIdList = conditionList.stream().map(SqfwSummaryPdfExcelCollForAdminDTO::getSumId).collect(Collectors.toList());

        String serviceType = conditionList.get(0).getServiceType();

        //String fileNameSuffix = StrFormatter.format("集合{}", DateUtil.format(new Date(), "yyyyMMddHHmmss"));
        //zhangp 2023-12-12 据产品要求去掉集合两字
        String fileNameSuffix = DateUtil.format(new Date(), "yyyyMMddHHmmss");
        if (serviceType.equals("安全排查")) {
            return generateFireHiddenCollectionExcelForApp(downType, sumIdList, null, userId, fileIndex, fileNameSuffix);
        }

        if (serviceType.equals("垃圾分类")) {
            return generateRefuseClassifyExcelForApp(downType, sumIdList, fileNameSuffix);
        }

        if (serviceType.equals("入户调查")) {
            return generateHouseSurveyFormExcelForApp(downType, sumIdList, excelType, fileNameSuffix);
        }

        if (SupportBusinessTypeEnum.MEET_RECORD.getName().equals(serviceType)) {
            return meetingMinutesService.mergeExcelLedgerByIds(sumIdList);
        }

        if (SupportBusinessTypeEnum.OLD_CARE_SERVICE.getName().equals(serviceType)) {
            return oldCareServiceStationExcelService.generateExcelColForApp(sumIdList);
        }

        return null;
    }

    private List<HouseSurveyFormPlaceVO> sortForHouseSurveyByPlace(List<HouseSurveyFormPlaceVO> tempResultList, List<HouseSurveyFormPlaceVO> noA4List) {

        List<HouseSurveyFormPlaceVO> resultList = Lists.newArrayList();

        tempResultList = tempResultList.stream().sorted(
                Comparator.comparing(HouseSurveyFormPlaceVO::getA1Char)
                        .thenComparing(HouseSurveyFormPlaceVO::getA4)
                        .thenComparing(HouseSurveyFormPlaceVO::getA5)
        ).collect(Collectors.toList());

        noA4List = noA4List.stream().sorted(
                Comparator.comparing(HouseSurveyFormPlaceVO::getA1Char)
                        .thenComparing(HouseSurveyFormPlaceVO::getA5)
        ).collect(Collectors.toList());


        resultList.addAll(tempResultList);
        resultList.addAll(noA4List);
        return resultList;
    }

    /**
     * 根据表单数据和工作流船舰垃圾分类的pdf
     *
     * @param userId       用户id
     * @param completeFlow 工作流
     * @param formSummary  表单数据
     */
    private void generateRefuseClassifyPDFByFlowAndForm(String userId, Sqfw_completeflow completeFlow, Sqfw_formSummary formSummary) {

        System.out.println("开始创建垃圾分类排查表PDF");

        // 生成word和pdf
        String unitId = formSummary.getUnitId();
        Sys_unit unit = sysUnitService.fetch(unitId);

        FireHiddenCheckWordDTO wordDTO = new FireHiddenCheckWordDTO();
        BeanUtil.copyProperties(formSummary, wordDTO);
        wordDTO.setCompleteUnitName(unit.getName());

        String createUserSignature = getCreateUserSignature(completeFlow.getUserId());
        wordDTO.setRefuseClassifyCreateUserSigUrl(createUserSignature);

        try {
            GenerateWordAndPdfUrlVO wordVO = RefuseClassifyOfficeUtils.createWordAndPdfUrlCheckForm(wordDTO);
            if (StringUtils.isBlank(wordVO.getPdfUrl())) {
                completeFlow.setResourcePath("error");
            } else {
                completeFlow.setResourcePath(wordVO.getPdfUrl());
                sqfwCompleteflowService.update(completeFlow);

                // 发送一个websocket通知前端
                WebSocketMsgDTO msgDTO = new WebSocketMsgDTO();
                msgDTO.setType(SqfwWebSocketMsgConstants.work_flow_refuse_classify_pdf_created);
                msgDTO.setMsg("垃圾分类表PDF生成成功");
                sqfwWebSocket.sendMessage(userId, msgDTO);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("创建消防排查PDF失败");
            completeFlow.setResourcePath("error");
            sqfwCompleteflowService.update(completeFlow);
        }
    }

    private void generateHouseSurveyFormPDFByFlowAndForm(String userId, Sqfw_completeflow completeFlow, Sqfw_formSummary formSummary) {

        System.out.println("开始创建入户调查表PDF");

        // 生成word和pdf
        String unitId = formSummary.getUnitId();
        Sys_unit unit = sysUnitService.fetch(unitId);

        HouseSurveyWordDTO wordDTO = new HouseSurveyWordDTO();
        BeanUtil.copyProperties(formSummary, wordDTO);
        wordDTO.setCompleteUnitName(unit.getName());

        try {
            GenerateWordAndPdfUrlVO wordVO = HouseSurveyFormOfficeUtils.createWordAndPdfUrlCheckForm(wordDTO);
            if (StringUtils.isBlank(wordVO.getPdfUrl())) {
                completeFlow.setResourcePath("error");
            } else {
                completeFlow.setResourcePath(wordVO.getPdfUrl());
                sqfwCompleteflowService.update(completeFlow);

                // 发送一个websocket通知前端
                WebSocketMsgDTO msgDTO = new WebSocketMsgDTO();
                msgDTO.setType(SqfwWebSocketMsgConstants.work_flow_house_survey_form_pdf_created);
                msgDTO.setMsg("入户调查表PDF生成成功");
                sqfwWebSocket.sendMessage(userId, msgDTO);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("创建入户调查表PDF失败");
            completeFlow.setResourcePath("error");
            sqfwCompleteflowService.update(completeFlow);
        }
    }

    /**
     * 在生成文档之前插入工作流和表单的数据
     *
     * @param userId              用户id
     * @param formSummaryWithFlow 工作流
     * @param formSummaryType     表单数据
     */
    private void insertWordFlowAndFormSummary(String userId, SqfwFormSummaryWithFlowDTO formSummaryWithFlow, String formSummaryType) {
        Trans.exec(() -> {
            Sys_user sysUser = sysUserService.fetch(userId);

            // 首先现需要生成一个工作流信息
            SqfwCompleteFlowUpdateDTO completeFlow = formSummaryWithFlow.getCompleteFlow();
            completeFlow.setResourcePath("creating");
            sqfwCompleteflowService.insertCompleteflow(completeFlow, sysUser);

            // 更新工单表中的最新工作流备注
            sqfwWorkorderService.updateLatestFlowRemarkWithCompleteFlow(completeFlow.getWorkerId(), completeFlow.getFileType(), false);

            // 然后生成一个表单数据
            Sqfw_formSummary formSummary = formSummaryWithFlow.getFormSummary();
            formSummary.setCreateDate(new Date());
            formSummary.setCreateBy(userId);
            formSummary.setSqfwId(completeFlow.getSqfwId());
            formSummary.setWorkOrderId(completeFlow.getWorkerId());
            formSummary.setSqfwFlowId(completeFlow.getId());
            formSummary.setCreateUserName(sysUser.getUsername());
            formSummary.setCreateUserPhone(sysUser.getLoginname());
            formSummary.setType(formSummaryType);
            // wzy 2023-11-13 由后端赋值这一张表的四级数据
            formSummary.setProvince(sysUser.getProvince());
            formSummary.setCity(sysUser.getCity());
            formSummary.setCounty(sysUser.getCounty());
            formSummary.setDistrict(sysUser.getDistrict());
            //formSummary.setCommunity(sysUser.getCommunity());

            // 如果是市南社区的话，如果只传了整改后照片，那么需要查询出之前最近的一次的整改前照片机型赋值
            String unitName = formSummary.getUnitName();
            if (unitName.contains("油榨街")) {
                if (formSummaryType.equals(SqfwFormSummaryConstants.safety_check)) {
                    // 如果整改前照片为空 并且 整改后照片不为空 那么就进行操作
                    if (StringUtils.isBlank(formSummary.getA11()) && StringUtils.isNotBlank(formSummary.getA12())) {
                        // 根据 场所名称 和 类型进行取值
                        Cnd cnd = Cnd.NEW();
                        cnd.and("a1", "=", formSummary.getA1())
                                .and("type", "=", SqfwFormSummaryConstants.safety_check)
                                .and("a41", "is not ", null)
                                .and("a41", "!=", "");
                        cnd.orderBy("fill_date", "desc");
                        Sqfw_formSummary queryFormSummary = fetch(cnd);
                        if (queryFormSummary != null) formSummary.setA11(queryFormSummary.getA11());
                    }
                }
            }

            if (unitName.contains("金元") || unitName.contains("润兴") || unitName.contains("金阳")) {
                if (formSummaryType.equals(SqfwFormSummaryConstants.safety_check)) {
                    if (StringUtils.isNotBlank(formSummary.getA45()) && formSummary.getA45().equals("已整改")
                            && StringUtils.isNotBlank(formSummary.getA11())
                            && StringUtils.isBlank(formSummary.getA10())) {
                        // 根据 场所名称 和 类型进行取值
                        Cnd cnd = Cnd.NEW();
                        cnd.and("a1", "=", formSummary.getA1())
                                .and("type", "=", SqfwFormSummaryConstants.safety_check)
                                .and("a45", "=", "未整改");
                        cnd.orderBy("fill_date", "desc");
                        Sqfw_formSummary queryFormSummary = fetch(cnd);
                        if (queryFormSummary != null) formSummary.setA10(queryFormSummary.getA10());
                    }
                }
            }

            if (unitName.contains("大营路")) {
                if (formSummaryType.equals(SqfwFormSummaryConstants.safety_check)) {
                    // 根据 场所名称、类型、unitId ，填表时间逆序排序来获取上一次填表数据
                    Cnd cnd = Cnd.NEW();
                    cnd.and("a1", "=", formSummary.getA1())
                            .and("type", "=", SqfwFormSummaryConstants.safety_check)
                            .and("unit_id", "=", formSummary.getUnitId());
                    cnd.orderBy("fill_date", "desc");
                    Sqfw_formSummary lastCheckSummary = fetch(cnd);
                    sqfwFormSummaryService.popularDaYingLuStreetLastCheckSummary(lastCheckSummary, formSummary);
                    updateIgnoreNull(lastCheckSummary);

                    // wzy 2023-11-2 如果当前只提交了一张整改后的照片，那么就需要把上一次提交的违规住人的照片赋值下来
                    if (StringUtils.isBlank(formSummary.getA39()) && StringUtils.isNotBlank(formSummary.getA40())) {
                        if (StringUtils.isNotBlank(lastCheckSummary.getA39())) {
                            formSummary.setA39(lastCheckSummary.getA39());
                        }
                    }
                }
            }

            // 如果是入户调查
            if (formSummaryType.equals(SqfwFormSummaryConstants.house_survey_form)) {
                if (StringUtils.isNotBlank(formSummary.getA7())) {
                    List<Sqfw_cenSusPaper> paperList = sqfwCenSusPaperService.addOrUpdateByFormSummaryStr(formSummary.getA7(), formSummary);
                    String paperA7Str = JSONObject.toJSONString(paperList);
                    formSummary.setA7(paperA7Str);
                }
            }
            log.info("insert formSummary request param: {}", JSONObject.toJSONString(formSummary));
            insert(formSummary);
        });
    }


    /**
     * 根据社区的名称创建消防安全排查表
     *
     * @param wordDTO 消防安全工单
     * @return 结果
     */
    private GenerateWordAndPdfUrlVO createFireHiddenCheckFormWordAndPDFByUnitName(FireHiddenCheckWordDTO wordDTO) {
        String unitName = wordDTO.getUnitName();

        Sys_user sysUser = sysUserService.getUserById(wordDTO.getCreateBy());
        wordDTO.setCreateUserName(sysUser.getUsername());

        wordDTO.setProvince(sysUser.getProvince());
        wordDTO.setCity(sysUser.getCity());
        wordDTO.setDistrict(sysUser.getDistrict());
        wordDTO.setCounty(sysUser.getCounty());
        //wordDTO.setCommunity(sysUser.getCommunity());

        GenerateWordAndPdfUrlVO resultVO;

        if (unitName.contains("油榨街")) {
            String a18 = wordDTO.getA18();
            Map<String, List<String>> userSignatureMap = getUserSignatureMap(a18, wordDTO.getUnitId());
            wordDTO.setA18NoSignatureUsers(userSignatureMap.get("noSig"));
            wordDTO.setA18UserSignatureUrls(userSignatureMap.get("sig"));

            resultVO = FireHiddenOfficeUtils.createFireHiddenYouZhaJieShiNanSQ(wordDTO);
        } else if (unitName.contains("金元") || unitName.contains("润兴") || unitName.contains("金阳")) {
            // a18 排查人员
            String a18 = wordDTO.getA18();
            Map<String, List<String>> userSignatureMap = getUserSignatureMap(a18, wordDTO.getUnitId());

            JinYangStreetFireHiddenCheckWordDTO jinYangWordDTO = new JinYangStreetFireHiddenCheckWordDTO();
            BeanUtil.copyProperties(wordDTO, jinYangWordDTO);

            // 含有签名图片的和没有签名图片的
            jinYangWordDTO.setJinYuanA18NoSigUsers(userSignatureMap.get("noSig"));
            jinYangWordDTO.setJinYuanA18SigUrls(userSignatureMap.get("sig"));
            jinYangWordDTO.setCompleteUnitName(unitName);
            jinYangWordDTO.setUnitName(unitName);

            String createUserSigUrl = getCreateUserSignature(wordDTO.getCreateBy());
            jinYangWordDTO.setJinYuanFireHiddenCreateUserSig(createUserSigUrl);

            // 两个社区文档的标题有些许差别
            if (unitName.contains("金元")) {
                jinYangWordDTO.setVisAndInsDocTitlePrefix(StrFormatter.format("金阳街道金元社区九小场所{}", DateUtil.format(jinYangWordDTO.getFillDate(), "yyyy年MM月dd日")));
                jinYangWordDTO.setStandingBookDocTitlePrefix("金阳街道金元社区");
                jinYangWordDTO.setGasInsDocTitlePrefix("金阳街道金元社区");
                jinYangWordDTO.setGasRecDocTitlePrefix("观山湖区金阳街道（金元社区）");
            }

            if (unitName.contains("润兴")) {
                jinYangWordDTO.setVisAndInsDocTitlePrefix(StrFormatter.format("金阳街道润兴居委会{}", DateUtil.format(jinYangWordDTO.getFillDate(), "yyyy年MM月dd日")));
                jinYangWordDTO.setStandingBookDocTitlePrefix("金阳街道润兴居委会");
                jinYangWordDTO.setGasInsDocTitlePrefix("金阳街道润兴居委会");
                jinYangWordDTO.setGasRecDocTitlePrefix("观山湖区金阳街道（润兴居委会）");
            }

            resultVO = FireHiddenOfficeUtils.createFireHiddenJinYuanSQ(jinYangWordDTO);
        } else if (unitName.contains("大营路") ||
                (StringUtils.isNotBlank(wordDTO.getArea()) && wordDTO.getArea().contains("大营路"))) {
            DaYingLuStreetFireHiddenCheckWordDTO daYingLuWordDTO = new DaYingLuStreetFireHiddenCheckWordDTO();
            BeanUtil.copyProperties(wordDTO, daYingLuWordDTO);

            String a18 = wordDTO.getA18();
            Map<String, List<String>> userSignatureMap = getUserSignatureMap(a18, wordDTO.getUnitId());

            // 含有签名图片的和没有签名图片的
            daYingLuWordDTO.setA18NoSignatureUsers(userSignatureMap.get("noSig"));
            daYingLuWordDTO.setA18UserSignatureUrls(userSignatureMap.get("sig"));

            // 根据a1和unitId查询上一次检查的结果
            DaYingLuStreetFireHiddenCheckWordDTO lastCheckWordDTO = getLastCheckWordDTOForDaYingLuFireHidden(
                    daYingLuWordDTO.getUnitId(), daYingLuWordDTO.getA1(), daYingLuWordDTO.getType(), daYingLuWordDTO.getFillDate());
            daYingLuWordDTO.setLastCheckFormSummary(lastCheckWordDTO);

            resultVO = DaYingLuStreetFireHiddenOfficeUtils.createFireHiddenPDF(daYingLuWordDTO);

        } else if (unitName.contains(StreetEnum.WEI_QING_MEN_STREET.getName())) {
            //威清门
            FireHiddenOfficeWordDTO fireHiddenOfficeWordDTO = new FireHiddenOfficeWordDTO();
            BeanUtil.copyProperties(wordDTO, fireHiddenOfficeWordDTO);
            //获取上一次的巡查
            Cnd cnd = Cnd.NEW();
            cnd.and("unit_id", "=", fireHiddenOfficeWordDTO.getUnitId())
                    .and("a1", "=", fireHiddenOfficeWordDTO.getA1())
                    .and("sqfw_id", "=", fireHiddenOfficeWordDTO.getSqfwId())
                    .and("fill_date", "<", fireHiddenOfficeWordDTO.getFillDate());
            cnd.orderBy("fill_date", "desc");
            Sqfw_formSummary formSummary = this.fetch(cnd);
            FireHiddenOfficeWordDTO upFormSummary = new FireHiddenOfficeWordDTO();
            BeanUtil.copyProperties(formSummary, upFormSummary);
            fireHiddenOfficeWordDTO.setUpFormSummary(formSummary != null ? upFormSummary : null);
            resultVO = WeiQingMenStreetFireHiddenOfficeUtils.createFireHiddenPDF(fireHiddenOfficeWordDTO);
            //设置电话号码
            resultVO.setPhone(fireHiddenOfficeWordDTO.getA4());
        } else if (unitName.contains(StreetEnum.YUN_TAN_STREET.getName())) {
            //宇虹社区
            YuHongMenStreetFireHiddenCheckWordDTO yuHongWordDTO = new YuHongMenStreetFireHiddenCheckWordDTO();
            BeanUtil.copyProperties(wordDTO, yuHongWordDTO);
            resultVO = YuHongMenStreetFireHiddenOfficeUtils.createFireHiddenPDF(yuHongWordDTO);
            //设置电话号码
            resultVO.setPhone(yuHongWordDTO.getA4());
        } else {
            // 花果园 生成消防安全排查pdf
            String a18 = wordDTO.getA18();
            Map<String, List<String>> userSignatureMap = getUserSignatureMap(a18, wordDTO.getUnitId());
            wordDTO.setA18NoSignatureUsers(userSignatureMap.get("noSig"));
            wordDTO.setA18UserSignatureUrls(userSignatureMap.get("sig"));
            resultVO = FireHiddenOfficeUtils.createFireHiddenHuaGuoYuan(wordDTO);
        }

        return resultVO;
    }


    /**
     * 当生成pdf和excel之后需要发送短信通知
     *
     * @param userId          用户id
     * @param phone           用户电话
     * @param messageSendFlag 成功标志
     */
    private void sendMsgAfterFireHiddenPdfOrExcel(String userId, String phone, boolean messageSendFlag) {
        // 发送一个websocket通知前端
        WebSocketMsgDTO msgDTO = new WebSocketMsgDTO();
        msgDTO.setType(SqfwWebSocketMsgConstants.work_flow_fire_hazards_pdf_created);
        msgDTO.setMsg("消防安全Excel生成成功");
        sqfwWebSocket.sendMessage(userId, msgDTO);

        if (messageSendFlag) {
            // 发送短信通知被检查人手机
            SmsMsgDTO smsMsgDTO = new SmsMsgDTO();
            smsMsgDTO.setPhoneNumberSet(Sets.newHashSet(phone));

            String url = DfxyWeChatMPUtils.generatedDfxyMiniProgramUrl("/pages/user-center/safeList");
            if (StringUtils.isNotBlank(url)) {
                ztSmsService.ztSendSafetyCheckInfoMsg(phone, url);
            }
        }
    }

    private Map<String, List<String>> getUserSignatureMap(String nameStr, String unitId) {
        List<String> picUrlList = Lists.newArrayList();
        List<String> noPicUserNameList = Lists.newArrayList();

        String[] userNameArray = nameStr.split(",");
        List<Sys_user> checkUserList = sysUserService.query(Cnd.NEW().and("username", "in", Sets.newHashSet(userNameArray)).and("unitid", "=", unitId));
        for (Sys_user checkUser : checkUserList) {
            String signatureUrl = checkUser.getSignatureUrl();
            if (StringUtils.isNotBlank(signatureUrl)) {
                if (signatureUrl.startsWith("/"))
                    signatureUrl = NutzIocUtils.getOssPrefix() + signatureUrl;
                picUrlList.add(signatureUrl);
            } else {
                noPicUserNameList.add(checkUser.getUsername());
            }
        }

        Map<String, List<String>> resultMap = Maps.newHashMap();
        resultMap.put("sig", picUrlList);
        resultMap.put("noSig", noPicUserNameList);

        return resultMap;
    }

    /**
     * 获取系统用户的签名图片
     *
     * @param createBy 系统用户id
     * @return 结果
     */
    private String getCreateUserSignature(String createBy) {
        Sys_user sysUser = sysUserService.fetch(createBy);
        if (sysUser == null) return null;
        return sysUser.getSignatureUrl();
    }

    /**
     * 大营路街道消防安全根据a1,type=安全排查和unitId来查询上一次填表的数据
     *
     * @return 结果
     */
    private DaYingLuStreetFireHiddenCheckWordDTO getLastCheckWordDTOForDaYingLuFireHidden(String unitId, String a1, String type, Date fillDate) {
        Cnd cnd = Cnd.NEW();
        cnd.and("unit_id", "=", unitId).and("a1", "=", a1).and("type", "=", type).and("fill_date", "<", fillDate);
        cnd.orderBy("fill_date", "desc");
        Sqfw_formSummary lastCheckFormSummary = fetch(cnd);
        if (lastCheckFormSummary == null) return null;

        // wzy 2023-10-11 如果上一次a61第四组没有问题的话就不处理
        String a61 = lastCheckFormSummary.getA61();
        if (StringUtils.isBlank(a61)) return null;
        List<String> a61List = JSONObject.parseArray(a61, String.class);
        Set<String> a61_4Set = Sets.newHashSet(JSONObject.parseArray(a61List.get(5), String.class));
        a61_4Set.remove("");
        if (a61_4Set.size() == 0) return null;

        DaYingLuStreetFireHiddenCheckWordDTO daYingLuWordDTO = new DaYingLuStreetFireHiddenCheckWordDTO();
        BeanUtil.copyProperties(lastCheckFormSummary, daYingLuWordDTO);

        String a18 = lastCheckFormSummary.getA18();
        Map<String, List<String>> userSignatureMap = getUserSignatureMap(a18, lastCheckFormSummary.getUnitId());

        // 含有签名图片的和没有签名图片的
        daYingLuWordDTO.setA18NoSignatureUsers(userSignatureMap.get("noSig"));
        daYingLuWordDTO.setA18UserSignatureUrls(userSignatureMap.get("sig"));

        return daYingLuWordDTO;
    }


    /**
     * 根据summaryId下载Excel（后台管理系统）
     * Add by LJT 2023-10-10
     *
     * @param sumIds         汇总Id列表
     * @param workOrderId    订单Id
     * @param userId         用户Id
     * @param fileIndex      下载模板类型
     * @param fileNameSuffix 文件名称
     * @param downType       下载类型 excel, Zip
     * @param queryTime      查询时间
     * @return 文件所在路径
     */

    private String generateFireHiddenCollectionExcelForBM(String downType, List<String> sumIds, String workOrderId, String userId,
                                                          String fileType, Integer fileIndex, String fileNameSuffix, String... queryTime) {
        if (sumIds.size() == 0) {
            return "";
        }

        // 短信下载
        if (fileIndex == 400) {
            return sqfwSmsMsgService.downExcel(sumIds);
        }

        Cnd cnd = Cnd.NEW();

        cnd.and("id", "in", sumIds);

        if (StringUtils.isNotBlank(workOrderId)) {
            cnd.and("work_order_id", "=", workOrderId);
        }

        if (StringUtils.isNotBlank(fileType)) {
            cnd.and("type", "=", fileType);
        }

        cnd.orderBy("fill_date", "asc");

        List<Sqfw_formSummary> formSummaryList = query(cnd);

        String sqfwId = fetch(sumIds.get(0)).getSqfwId();
        Sqfw_servicerecord serviceRecord = sqfwServicerecordService.fetch(sqfwId);

        AbstractExcelParamDTO excelParamDTO = new AbstractExcelParamDTO();
        if ("zip".equals(downType)) {
            excelParamDTO.setReturnWebUrlFlag(false);
        }
        excelParamDTO.setFillDate(new Date());
        excelParamDTO.setSignalPersonImgUrl("");
        excelParamDTO.setUnitNamePrefix(serviceRecord.getCompanyName());
        excelParamDTO.setCompleteUnitName(serviceRecord.getCompanyName());

        excelParamDTO.setFormSummaryList(formSummaryList);

        String unitName = "";
        try {
            Sys_user createServiceRecordUser = sysUserService.getUserById(serviceRecord.getUser_id());
            if (StringUtils.isNotEmpty(serviceRecord.getUser_id())) {
                String unitId = createServiceRecordUser.getUnitId();
                Sys_unit unit = sysUnitService.getMasterCompany(unitId);
                unitName = unit.getName();
            }
        } catch (BaseException bex) {
            log.info("查询异常：{}", bex.getMessage());
            unitName = formSummaryList.get(0).getCounty();
        } finally {
            if (StringUtils.isEmpty(unitName)) {
                throw new BaseException("用户不存在");
            }
        }

        String titleUnitName = serviceRecord.getCounty();
        if (Strings.isBlank(titleUnitName)) {
            titleUnitName = unitName;
        }
        if (!titleUnitName.contains("街道")) {
            titleUnitName += "街道";
        }
        if (Strings.isNotBlank(titleUnitName)) {
            titleUnitName = titleUnitName.substring(0, titleUnitName.lastIndexOf("街道") + 2);
        }
        // 后台管理系统生成文件表头单位名称，如果社区是直属，就只显示到街道 add by LJT 2023-10-25
        String unitNameSub = unitName;
        Cnd cndUser = Cnd.NEW();
        cndUser.and("id", "=", userId);
        Sys_user sysUser = sysUserService.fetch(cndUser);
        if ("直属".equals(sysUser.getCommunity())) {
            unitNameSub = StringUtils.substring(unitNameSub, 0, StringUtils.substringBeforeLast(unitNameSub, "街道").length() + 2);
        }

        // 油榨街街道的
        if (unitName.contains("油榨街")) {
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            return FireHiddenOfficeUtils.createFireHiddenExcelYouZhaJieShiNanSQ(excelParamDTO, null);
        }

        // 金元社区的
        if (unitName.contains("金元") || unitName.contains("润兴") || unitName.contains("金阳")) {
            Sys_user createFileUser = sysUserService.fetch(userId);

            FireHiddenExcelParamJinYuanSQDTO dto = new FireHiddenExcelParamJinYuanSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);

            if (unitName.contains("金元")) {
                dto.setVisAndInsExcelTitlePrefix(StrFormatter.format("金阳街道金元社区九小场所{}", DateUtil.format(dto.getFillDate(), "yyyy年MM月")));
                dto.setGasInsExcelTitlePrefix("金阳街道金元社区");
                dto.setGasRecExcelTitlePrefix("观山湖区金元社区");
                dto.setFireSafetyExcelTitlePrefix("金阳街道金元社区");
            }

            if (unitName.contains("润兴")) {
                dto.setVisAndInsExcelTitlePrefix(StrFormatter.format("金阳街道润兴居委会{}", DateUtil.format(dto.getFillDate(), "yyyy年MM月")));
                dto.setGasInsExcelTitlePrefix("金阳街道润兴居委会");
                dto.setGasRecExcelTitlePrefix("观山湖区润兴居委会");
                dto.setFireSafetyExcelTitlePrefix("金阳街道润兴居委会");
            }

            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setCompleteUnitName(titleUnitName);

            if (null == fileIndex) {
                fileIndex = 1;
            }
            dto.setFileIndex(fileIndex);

            // 如果是生成台账，则需要查询一些数据用来进行填装
            if (0 == fileIndex || 4 == fileIndex) {
                List<Sqfw_formSummary> jinYuanFormSummaryList = dto.getFormSummaryList();

                Map<String, Sqfw_formSummary> latestA1AndA13Map = Maps.newHashMap();
                for (Sqfw_formSummary jinYuanFormSummary : jinYuanFormSummaryList) {
                    String jinYuanA1 = jinYuanFormSummary.getA1();
                    String jinYuanA13 = jinYuanFormSummary.getA13();

                    if (jinYuanA13.equals("整改完成")) {
                        Cnd jinYuanCnd = Cnd.NEW();
                        jinYuanCnd.and("a1", "=", jinYuanA1).and("a13", "=", "有");
                        jinYuanCnd.orderBy("fill_date", "desc");
                        Sqfw_formSummary jinYuanQueryFormSummary = fetch(jinYuanCnd);
                        if (jinYuanQueryFormSummary != null && !jinYuanQueryFormSummary.getId().equals(jinYuanFormSummary.getId()))
                            latestA1AndA13Map.put(jinYuanQueryFormSummary.getA1(), jinYuanQueryFormSummary);
                    }

                }
                dto.setLatestA1AndA13Map(latestA1AndA13Map);
            }

            // 生成文件
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            VisAndInsForJinYangJieDaoExcelVO jinYuanFireHiddenExcelVO = null;
            jinYuanFireHiddenExcelVO = FireHiddenOfficeUtils.createFireHiddenExcelJinYuanSQ(dto, null);
            if (jinYuanFireHiddenExcelVO.getErrorStatus()) return null;

            if (fileIndex == 1) return jinYuanFireHiddenExcelVO.getVisAndInsExcelUrl();
            if (fileIndex == 2) return jinYuanFireHiddenExcelVO.getGasInsExcelUrl();
            if (fileIndex == 3) return jinYuanFireHiddenExcelVO.getGasRecExcelUrl();
            if (fileIndex == 4) return jinYuanFireHiddenExcelVO.getStandingBookExcelUrl();

            if (fileIndex == 0)
                return StrFormatter.format("{},{},{},{}", jinYuanFireHiddenExcelVO.getVisAndInsExcelUrl(),
                        jinYuanFireHiddenExcelVO.getGasInsExcelUrl(), jinYuanFireHiddenExcelVO.getGasRecExcelUrl(), jinYuanFireHiddenExcelVO.getStandingBookExcelUrl());
        }

        //大营路街道
        if (unitName.contains("大营路")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamDaYingLuSQDTO dto = new FireHiddenExcelParamDaYingLuSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setStreet(createFileUser.getCounty());
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            dto.setArea(serviceRecord.getDistrict());
            dto.setStreet(serviceRecord.getCounty());
            dto.setDataSource(CommonFormOfficeForAdminUtils.APPLICATION_ADMIN);

            List<Sqfw_formSummary> preFormSummaryList = dto.getFormSummaryList();
            Map<String, Sqfw_formSummary> lastFormSummaryCheckMap = Maps.newHashMap();
            for (Sqfw_formSummary formSummary : preFormSummaryList) {
                Cnd dylLastCheckCND = Cnd.NEW();
                dylLastCheckCND.and(Sqfw_formSummary::getA1, "=", formSummary.getA1())
                        .and(Sqfw_formSummary::getUnitId, "=", formSummary.getUnitId());
                dylLastCheckCND.orderBy(Sqfw_formSummary::getFillDate, "desc");
                Sqfw_formSummary lastCheckFormSummary = fetch(dylLastCheckCND);
                if (lastCheckFormSummary != null && !lastCheckFormSummary.getId().equals(formSummary.getId())) {
                    lastFormSummaryCheckMap.put(formSummary.getId(), lastCheckFormSummary);
                }
            }
            dto.setLastFormSummaryCheckMap(lastFormSummaryCheckMap);
            return DaYingLuStreetFireHiddenExcelUtils.createFireHiddenExcelDaYingLuSQ(dto, null);
        }

        //威清门街道
        if (unitName.contains("威清门")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamWeiQingMenSQDTO dto = new FireHiddenExcelParamWeiQingMenSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setProvince(createFileUser.getProvince());
            dto.setCity(createFileUser.getCity());
            dto.setDistrict(createFileUser.getDistrict());
            dto.setCounty(createFileUser.getCounty());
            //dto.setCommunity(createFileUser.getCommunity());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            //zhangp 2023-12-22 提出此部分共用
            return weiQingMenStreetFireHiddenExcel(dto, sumIds, null, queryTime);
            /*
            // 灾防控排查整治进度统计表(这个是特别定制表，所以单独处理)
            if (fileIndex == 13) {
                String ids = JSON.toJSONString(sumIds).replace("[","(").replace("]", ")").replace("\"","'");
                String sql = "select * from shequ.sqfw_censuspaper " +
                        "where county ='"+ StreetEnum.WEI_QING_MEN_STREET.getName()+"' and community in " +
                        "(select community from shequ.sqfw_form_summary " +
                        "where community is not null and id in "+ids+")";
                if (queryTime.length > 0 && StringUtils.isNotBlank(queryTime[0])) {
                    String times[] = queryTime[0].split(",");
                    sql = sql + " and create_date > "+DateUtil.format(new Date(Long.parseLong(times[0])), "yyy-MM-dd")+"" +
                            " and create_date < "+DateUtil.format(new Date(Long.parseLong(times[1])), "yyy-MM-dd");
                }
                List<Sqfw_cenSusPaper> cenSusPaperList = sqfwCenSusPaperService.listEntity(Sqls.create(sql));
                return WeiQingMenStreetFireHiddenExcelUtils.createImprovementProgressStatistics(formSummaryList, cenSusPaperList);
            }

            return WeiQingMenStreetFireHiddenExcelUtils.createFireHiddenExcelSQ(dto, null);
            */
        }

        //宇虹社区
        if (unitName.contains("云潭街道")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamYuHongSQDTO dto = new FireHiddenExcelParamYuHongSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setCounty(createFileUser.getCounty());
            //dto.setCommunity(createFileUser.getCommunity());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            return YuHongMenStreetFireHiddenExcelUtils.createExcelSQ(dto, null);
        }

        // 其他社区的
        return FireHiddenOfficeUtils.createFireHiddenExcel(excelParamDTO, null);
    }

    @Override
    public String weiQingMenStreetFireHiddenExcel(FireHiddenExcelParamWeiQingMenSQDTO dto, List<String> sumIds, String isCheckData, String... queryTime) {
        // 灾防控排查整治进度统计表(这个是特别定制表，所以单独处理)
        if (dto.getFileIndex() == WeiQingMenExcelType.WINTER_TOMORROW_SPRING_FIRE_INSPECTION.getCode() && isCheckData == null) {
            String ids = JSON.toJSONString(sumIds).replace("[", "(").replace("]", ")").replace("\"", "'");
            String sql = "select * from shequ.sqfw_censuspaper " +
                    "where county ='" + StreetEnum.WEI_QING_MEN_STREET.getName() + "' and community in " +
                    "(select community from shequ.sqfw_form_summary " +
                    "where community is not null and id in " + ids + ")";
            if (queryTime.length > 0 && StringUtils.isNotBlank(queryTime[0])) {
                String times[] = queryTime[0].split(",");
                sql = sql + " and create_date > '" + DateUtil.format(new Date(Long.parseLong(times[0])), "yyy-MM-dd") + "'" +
                        " and create_date < '" + DateUtil.format(new Date(Long.parseLong(times[1])), "yyy-MM-dd") + "'";
            }
            List<Sqfw_cenSusPaper> cenSusPaperList = sqfwCenSusPaperService.listEntity(Sqls.create(sql));
            return WeiQingMenStreetFireHiddenExcelUtils.createImprovementProgressStatistics(dto.getFormSummaryList(), cenSusPaperList, isCheckData);
        }

        return WeiQingMenStreetFireHiddenExcelUtils.createFireHiddenExcelSQ(dto, isCheckData);
    }

    /**
     * 根据summaryId下载Excel集合（后台管理系统）
     * Add by LJT 2023-10-10
     */
    @Override
    public String generateServiceRecordExcelCollForBM(String downType, List<String> sumIdList, String userId, String fileType, Integer fileIndex) {
        if (sumIdList.size() == 0) {
            return null;
        }

        String fileNameSuffix = StrFormatter.format("集合{}", DateUtil.format(new Date(), "yyyyMMddHHmmss"));

        return generateFireHiddenCollectionExcelForBM(downType, sumIdList, null, userId, fileType, fileIndex, fileNameSuffix);
    }


    /**
     * 根据summaryId下载安全排查Excel（后台管理系统）
     * Add by LJT 2023-10-10
     */
    @Override
    public String generateServiceRecordExcelCollZipForBM(String county, List<ServiceRecordPdfOrExcelCollDTO> conditionList, String userId, List<Integer> fileIndexList, String excelType) {
        if (conditionList.size() == 0) {
            return null;
        }
        long startTime = System.currentTimeMillis();
        // 根据文件模板类型遍历合并生成Excel文件返回文件路径
        String fileNameSuffix = "";
        String tmpFilePath = "";
        List<String> excelFileDirs = new ArrayList<String>();
        if (null != fileIndexList && fileIndexList.size() > 0) {
            Set<Integer> fileIndexSet = new HashSet<Integer>(fileIndexList);
            for (Integer fileIndex : fileIndexSet) {
                tmpFilePath = generateServiceRecordExcelCollCopyApp("zip", conditionList, userId, fileIndex, excelType);
                if (null == tmpFilePath) {
                    continue;
                }
                excelFileDirs.add(tmpFilePath);
            }
        } else if (Strings.isNotBlank(excelType) && !excelType.contains("安全排查")) {
            tmpFilePath = generateServiceRecordExcelCollCopyApp("zip", conditionList, userId, null, excelType);
            excelFileDirs.add(tmpFilePath);
        } else {
            return null;
        }
        if (excelFileDirs.size() <= 0 || null == excelFileDirs.get(0)) {
            return null;
        }
        long modelTime = System.currentTimeMillis();
        System.out.println("生成文件时间：" + (modelTime - startTime) + "ms");
        // 压缩文件后返回压缩包路径
        if (null == county) {
            county = "街道";
        }
        File uploadFile = null;
        if (Strings.isBlank(county)) {
            Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
            county = sysUser.getCounty();
        }
        String fileName = county + "文档集合" + System.currentTimeMillis() + ".zip";
        String filePath = CommonFormOfficeForAdminUtils.getTempFilePath(fileName);
        try {
            CommonFormOfficeForAdminUtils.compressFileList(filePath, excelFileDirs);
            long zipTime = System.currentTimeMillis();
            System.out.println("打压缩包文件时间：" + (zipTime - modelTime) + "ms");
            // 上传华为云服务器后返回文件URL
            uploadFile = new File(filePath);
            SysOss sysOss = CommonFormOfficeForAdminUtils.uploadFile(uploadFile, fileName);
            long endTime = System.currentTimeMillis();
            System.out.println("上传压缩包文件时间：" + (endTime - zipTime) + "ms");
            return sysOss.getUrl();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ExecutionException e) {
            throw new RuntimeException(e);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        } finally {
            if (null != uploadFile) {
                uploadFile.delete();
            }
            for (String file : excelFileDirs) {
                FileUtil.del(file);
            }
            FileUtil.del(filePath);
        }
    }

    /**
     * 根据sqfwId下载Excel（后台管理系统任务记录）
     * Add by LJT 2023-10-10
     */
    @Override
    public String generateServiceSummaryExcelCollZipForBM(String type, String county, List<String> sumIds, String userId, List<Integer> fileIndexList, String excelType, String... queryTime) {
        if (sumIds.size() == 0) {
            return null;
        }
        long startTime = System.currentTimeMillis();
        // 根据文件模板类型遍历合并生成Excel文件返回文件路径
        String fileNameSuffix = "";
        String tmpFilePath = "";
        List<String> excelFileDirs = new ArrayList<String>();
        if ("垃圾分类".equals(type)) {
            List<SqfwSummaryPdfExcelCollForAdminDTO> conditionList = new ArrayList<>();
            for (String id : sumIds) {
                SqfwSummaryPdfExcelCollForAdminDTO sqfwDto = new SqfwSummaryPdfExcelCollForAdminDTO();
                sqfwDto.setSumId(id);
                sqfwDto.setServiceType("垃圾分类");
                conditionList.add(sqfwDto);
            }
            tmpFilePath = generateCheckRecordExcelColl("zip", conditionList, userId, 1, excelType);
            excelFileDirs.add(tmpFilePath);
        } else if ("入户调查".equals(type)) {
            tmpFilePath = generateHouseSurveyFormExcelForAdmin("zip", sumIds, excelType, "");
            excelFileDirs.add(tmpFilePath);
        } else {
            Set<Integer> fileIndexSet = new HashSet<Integer>(fileIndexList);
            for (Integer fileIndex : fileIndexSet) {
                //fileNameSuffix = StrFormatter.format("集合{}", DateUtil.format(new Date(), "yyyyMMddHHmmss"));
                fileNameSuffix = DateUtil.format(new Date(), "yyyyMMddHHmmss");
                tmpFilePath = generateFireHiddenCollectionExcelForBM("zip", sumIds, null, userId, "", fileIndex, fileNameSuffix, queryTime);
                if (null == tmpFilePath) {
                    continue;
                }
                excelFileDirs.add(tmpFilePath);
            }
        }
        if (excelFileDirs.size() <= 0 || null == excelFileDirs.get(0)) {
            return null;
        }
        long modelTime = System.currentTimeMillis();
        System.out.println("生成文件时间：" + (modelTime - startTime) + "ms");
        // 压缩文件后返回压缩包路径
        File uploadFile = null;
        if (Strings.isBlank(county)) {
            Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
            county = sysUser.getCounty();
        }
        String fileName = county + "文档集合" + System.currentTimeMillis() + ".zip";
        String filePath = CommonFormOfficeForAdminUtils.getTempFilePath(fileName);
        try {
            CommonFormOfficeForAdminUtils.compressFileList(filePath, excelFileDirs);
            long zipTime = System.currentTimeMillis();
            System.out.println("打压缩包文件时间：" + (zipTime - modelTime) + "ms");
            // 上传华为云服务器后返回文件URL
            uploadFile = new File(filePath);
            SysOss sysOss = CommonFormOfficeForAdminUtils.uploadFile(uploadFile, fileName);
            long endTime = System.currentTimeMillis();
            System.out.println("上传压缩包文件时间：" + (endTime - zipTime) + "ms");
            return sysOss.getUrl();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ExecutionException e) {
            throw new RuntimeException(e);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        } finally {
            if (null != uploadFile) {
                uploadFile.delete();
            }
            for (String file : excelFileDirs) {
                FileUtil.del(file);
            }
            FileUtil.del(filePath);
        }
    }


    /**
     * 大营路下载excel压缩包使用
     *
     * @param bean
     * @return
     */
    @Override
    public List<String> getDaYingLuExcelFileDownSumIdList(Sqfw_formSummaryForAdminVO bean) {
        Sql sql = Sqls.create(getQueryCondForDaYingLu(bean));
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<String> list = Lists.newArrayList();
            while (rs.next()) {
                list.add(rs.getString("id"));
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(String.class);
    }


    /**
     * 威清门下载excel压缩包使用
     *
     * @param bean
     * @return
     */
    @Override
    public List<String> getWeiQingMenExcelFileDownSumIdList(Sqfw_formSummaryForAdminVO bean) {
        Sql sql = Sqls.create(getQueryCondForWeiQingMen(bean));
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<String> list = Lists.newArrayList();
            while (rs.next()) {
                list.add(rs.getString("id"));
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(String.class);
    }

    @Override
    public List<Integer> checkDataForQuery(Sqfw_formSummaryForAdminVO bean) {
        Sql sql = Sqls.create(getQueryCondForWeiQingMen(bean));
        String s = sql.toString().replace("select id", "select *");
        List<Sqfw_formSummary> list = this.listEntity(Sqls.create(s));
        List<Integer> integers = sqfwFormSummaryService.checkParamWeiQingMenStreet(list);
        return integers;
    }

    @Override
    public List<Integer> checkDataForIds(List<String> ids) {
        Cnd cnd = Cnd.NEW();
        cnd.and("id", "in", ids);
        List<Sqfw_formSummary> list = sqfwFormSummaryService.query(cnd);
        List<Integer> integers = sqfwFormSummaryService.checkParamWeiQingMenStreet(list);
        return integers;
    }

    /**
     * 大营路下载pdf压缩包使用
     *
     * @param bean
     * @return
     */
    @Override
    public List<SqfwSummaryPdfExcelCollForAdminDTO> getDaYingLuPdfFileDownSumIdList(Sqfw_formSummaryForAdminVO bean) {
        Sql sql = Sqls.create(getQueryCondForDaYingLu(bean));
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<SqfwSummaryPdfExcelCollForAdminDTO> list = Lists.newArrayList();
            while (rs.next()) {
                SqfwSummaryPdfExcelCollForAdminDTO newCond = new SqfwSummaryPdfExcelCollForAdminDTO();
                newCond.setServiceType(rs.getString("type"));
                newCond.setSumId(rs.getString("id"));
                list.add(newCond);
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(SqfwSummaryPdfExcelCollForAdminDTO.class);
    }

    /**
     * 威清门下载pdf压缩包使用
     *
     * @param bean
     * @return
     */
    @Override
    public List<SqfwSummaryPdfExcelCollForAdminDTO> getWeiQingmenPdfFileDownSumIdList(Sqfw_formSummaryForAdminVO bean) {
        Sql sql = Sqls.create(getQueryCondForWeiQingMen(bean));
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<SqfwSummaryPdfExcelCollForAdminDTO> list = Lists.newArrayList();
            while (rs.next()) {
                SqfwSummaryPdfExcelCollForAdminDTO newCond = new SqfwSummaryPdfExcelCollForAdminDTO();
                newCond.setServiceType(bean.getType());
                newCond.setSumId(rs.getString("id"));
                list.add(newCond);
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(SqfwSummaryPdfExcelCollForAdminDTO.class);
    }

    /**
     * 大营路查询条件（后台管理系统）
     *
     * @param bean
     * @return
     */
    private String getQueryCondForDaYingLu(Sqfw_formSummaryForAdminVO bean) {
        StringBuffer sb = new StringBuffer(" select id, type from shequ.\"sqfw_form_summary\" where 1 = 1 ");
        if (Strings.isNotBlank(bean.getQueryAll())) {
            String queryAll = bean.getQueryAll();
            sb.append(" and ( a1 like '%").append(queryAll).append("%'")
                    .append(" OR a2 like '%").append(queryAll).append("%'")
                    .append(" OR a5 like '%").append(queryAll).append("%'")
                    .append(" OR a7 like '%").append(queryAll).append("%'")
                    .append(" OR a8 like '%").append(queryAll).append("%'")
                    .append(" OR a13 like '%").append(queryAll).append("%'")
                    .append(" OR a16 like '%").append(queryAll).append("%'")
                    .append(" OR community like '%").append(queryAll).append("%'")
                    .append(" OR create_user_name like '%").append(queryAll).append("%') ");
        }
        if (Strings.isNotBlank(bean.getA1())) {
            sb.append(" and a1 like '%").append(bean.getA1()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA2())) {
            sb.append(" and a2 like '%").append(bean.getA2()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA3())) {
            sb.append(" and a3 like '%").append(bean.getA3()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA5())) {
            sb.append(" and a5 like '%").append(bean.getA5()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA6())) {
            sb.append(" and a6 like '%").append(bean.getA6()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA7())) {
            sb.append(" and a7 like '%").append(bean.getA7()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA13())) {
            sb.append(" and a13 like '%").append(bean.getA13()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA16())) {
            sb.append(" and a16 like '%").append(bean.getA16()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA17())) {
            sb.append(" and a17 like '%").append(bean.getA17()).append("%'");
        }
        if (Strings.isNotBlank(bean.getProvince())) {
            sb.append(" and province = '").append(bean.getProvince()).append("'");
        }
        if (Strings.isNotBlank(bean.getCity())) {
            sb.append(" and city = '").append(bean.getCity()).append("'");
        }
        if (Strings.isNotBlank(bean.getCounty())) {
            sb.append(" and county = '").append(bean.getCounty()).append("'");
        }
        if (Strings.isNotBlank(bean.getDistrict())) {
            sb.append(" and district = '").append(bean.getDistrict()).append("'");
        }
        if (Strings.isNotBlank(bean.getCommunity())) {
            sb.append(" and community = '").append(bean.getCommunity()).append("'");
        }
        if (Strings.isNotBlank(bean.getCreateUserName())) {
            sb.append(" and create_user_name like '%").append(bean.getCreateUserName()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCreateUserPhone())) {
            sb.append(" and create_user_phone like '%").append(bean.getCreateUserPhone()).append("%'");
        }
        if (Strings.isNotBlank(bean.getType())) {
            StringBuffer subStr = new StringBuffer();
            String types[] = bean.getType().split("\\,");
            for (String type : types) {
                subStr.append("'").append(type).append("',");
            }
            String subStrCnd = subStr.substring(0, subStr.length() - 1);
            sb.append(" and type in (").append(subStrCnd).append(")");
        }
        //数据库中存在这种"0001-01-01 00:00:00.000" 历史数据， 需要过滤掉
        sb.append(" and fill_date > '0001-01-01 00:00:00.000'");

        if (Strings.isNotBlank(bean.getQueryTime())) {
            String time[] = bean.getQueryTime().split(",");
            Date date1 = new Date();
            date1.setTime(Long.parseLong(time[0]));
            Date date2 = new Date();
            date2.setTime(Long.parseLong(time[1]));
            SimpleDateFormat SDF_START = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sb.append(" and fill_date > '").append(SDF_START.format(date1)).append("' and fill_date < '").append(SDF_START.format(date2)).append("'");
        }

        // 增加巡查状态 add by LJT 2023-11-22
        if (Strings.isNotBlank(bean.getCheckStatus()) && CHECK_OUT.equals(bean.getCheckStatus())) {
            Date offsetDate = new Date();
            if ("安全排查".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            } else if ("社会治安".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.SDF_START.format(DateUtil.beginOfWeek(new Date())));
            }
            sb.append(" and ( fill_date >= '").append(offsetDate).append("' OR check_status != '1') ");
        }

        // 如果是查询未超时数据，只查询7天前的数据（需求）
        if (Strings.isNotBlank(bean.getCheckStatus()) && TO_DO_CHECK_OUT.equals(bean.getCheckStatus())) {
            sb.append(" and check_status = '").append(bean.getCheckStatus()).append("'");
            Date offsetDate = new Date();
            if ("安全排查".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            } else if ("社会治安".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.SDF_START.format(DateUtil.beginOfWeek(new Date())));
            }
            sb.append(" and fill_date < '").append(offsetDate).append("'");
        }

        // 数据统计页面 隐患数据跳转过来查询列表使用 add by LJT 2023-11-16
        if (Strings.isNotBlank(bean.getIssueStatus()) && "statistic".equals(bean.getIssueStatus())) {
            sb.append(" and issue_status is not null ");
            sb.append(" and issue_status != '正常' ");
        }

        // 非数据统计页面 是否存在隐患 add by LJT 2023-12-06
        if (Strings.isNotBlank(bean.getIssueStatus()) && !"statistic".equals(bean.getIssueStatus())) {
            if ("是".equals(bean.getIssueStatus())) {
                sb.append(" and issue_status is not null ");
                sb.append(" and issue_status != '正常' ");
                sb.append(" and issue_status != '无' ");
            } else if ("否".equals(bean.getIssueStatus())) {
                sb.append(" and issue_status = '正常' ");
            }
        }

        sb.append(" order by fill_date desc");
        return sb.toString();
    }

    /**
     * 威清门查询条件（后台管理系统）
     *
     * @param bean
     * @return
     */
    private String getQueryCondForWeiQingMen(Sqfw_formSummaryForAdminVO bean) {
        StringBuffer sb = new StringBuffer(" select id from shequ.\"sqfw_form_summary\" where 1 = 1 ");
        if (Strings.isNotBlank(bean.getQueryAll())) {
            String queryAll = bean.getQueryAll();
            sb.append(" and ( a1 like '%").append(queryAll).append("%'")
                    .append(" OR a3 like '%").append(queryAll).append("%'")
                    .append(" OR a5 like '%").append(queryAll).append("%'")
                    .append(" OR a6 like '%").append(queryAll).append("%'")
                    .append(" OR a7 like '%").append(queryAll).append("%'")
                    .append(" OR a17 like '%").append(queryAll).append("%'")
                    .append(" OR community like '%").append(queryAll).append("%'")
                    .append(" OR create_user_name like '%").append(queryAll).append("%'")
                    .append(" OR a61 like '%").append(queryAll).append("%')");
        }
        if (Strings.isNotBlank(bean.getA1())) {
            sb.append(" and a1 like '%").append(bean.getA1()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA2())) {
            sb.append(" and a2 like '%").append(bean.getA2()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA3())) {
            sb.append(" and a3 like '%").append(bean.getA3()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA5())) {
            sb.append(" and a5 like '%").append(bean.getA5()).append("%'");
        }
        if (StringUtils.isNotBlank(bean.getIsNightMarket())) {
            if ("是".equals(bean.getIsNightMarket())) {
                sb.append(" and a6 like '%临摊、夜市%'");
            } else if ("否".equals(bean.getIsNightMarket())) {
                sb.append(" and a6 not like '%临摊、夜市%'");
            }
        }
        if (Strings.isNotBlank(bean.getA6())) {
            sb.append(" and a6 like '%").append(bean.getA6()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA7())) {
            sb.append(" and a7 like '%").append(bean.getA7()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA13())) {
            sb.append(" and a13 like '%").append(bean.getA13()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA16())) {
            sb.append(" and a16 like '%").append(bean.getA16()).append("%'");
        }
        if (Strings.isNotBlank(bean.getA17())) {
            sb.append(" and a17 like '%").append(bean.getA17()).append("%'");
        }
        if (Strings.isNotBlank(bean.getProvince())) {
            sb.append(" and province like '%").append(bean.getProvince()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCity())) {
            sb.append(" and city like '%").append(bean.getCity()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCounty())) {
            sb.append(" and county like '%").append(bean.getCounty()).append("%'");
        }
        if (Strings.isNotBlank(bean.getDistrict())) {
            sb.append(" and district like '%").append(bean.getDistrict()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCommunity())) {
            sb.append(" and community like '%").append(bean.getCommunity()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCreateUserName())) {
            sb.append(" and create_user_name like '%").append(bean.getCreateUserName()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCreateUserPhone())) {
            sb.append(" and create_user_phone like '%").append(bean.getCreateUserPhone()).append("%'");
        }
        if (Strings.isNotBlank(bean.getType())) {
            sb.append(" and type like '%").append(bean.getType()).append("%'");
        }

        //数据库中存在这种"0001-01-01 00:00:00.000" 历史数据， 需要过滤掉
        sb.append(" and fill_date > '0001-01-01 00:00:00.000'");

        if (bean.getQueryTime() != null && Strings.isNotBlank(bean.getQueryTime())) {
            String time[] = bean.getQueryTime().split(",");
            Date date1 = new Date();
            date1.setTime(Long.parseLong(time[0]));
            Date date2 = new Date();
            date2.setTime(Long.parseLong(time[1]));
            SimpleDateFormat SDF_START = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sb.append(" and fill_date > '").append(SDF_START.format(date1)).append("' and fill_date < '").append(SDF_START.format(date2)).append("'");
        }
        // 增加巡查状态 add by LJT 2023-11-22
        if (Strings.isNotBlank(bean.getCheckStatus()) && CHECK_OUT.equals(bean.getCheckStatus())) {
            Date offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            sb.append(" and fill_date > '").append(offsetDate).append("'");
        }

        // 如果是查询未超时数据，只查询7天前的数据（需求）
        if (Strings.isNotBlank(bean.getCheckStatus()) && TO_DO_CHECK_OUT.equals(bean.getCheckStatus())) {
            sb.append(" and check_status = '").append(bean.getCheckStatus()).append("'");
            Date offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            sb.append(" and fill_date < '").append(offsetDate).append("'");
        }

        // 数据统计页面 隐患数据跳转过来查询列表使用 add by LJT 2023-11-16
        if (Strings.isNotBlank(bean.getIssueStatus()) && "statistic".equals(bean.getIssueStatus())) {
            sb.append(" and issue_status is not null ");
            sb.append(" and issue_status != '正常' ");
        }

        // 非数据统计页面 是否存在隐患 add by LJT 2023-12-06
        if (Strings.isNotBlank(bean.getIssueStatus()) && !"statistic".equals(bean.getIssueStatus())) {
            if ("是".equals(bean.getIssueStatus())) {
                sb.append(" and issue_status is not null ");
                sb.append(" and issue_status != '正常' ");
            } else if ("否".equals(bean.getIssueStatus())) {
                sb.append(" and issue_status = '正常' ");
            }
        }
        // 需求增加a61条件
        if (Strings.isNotBlank(bean.getA61())) {
            sb.append(" and a61 like '%").append(bean.getA61()).append("%'");
        }

        sb.append(" order by fill_date desc");
        return sb.toString();
    }

    @Override
    public String generateServiceRecordToPdfColl(List<ServiceRecordPdfOrExcelCollDTO> conditionList) {

        // 目前pdf存储于两个地方，一个是安全排查、垃圾分类、入户调查等业务的pdf存储在工作流中的sqfw_complete_flow的resource中
        // 另外的 12345、八单一表、简报、日报、其他任务都是存储于fileReport这张表中。
        List<PdfResourceSortBO> pdfBoList = Lists.newArrayList();

        Set<String> flowTypeSet = Sets.newHashSet("垃圾分类", "安全排查", "入户调查", "社会治安");
        Set<String> fileReportTypeSet = Sets.newHashSet("12345", "简报", "八单一表", "日报", "其他");

        Map<String, ServiceRecordPdfOrExcelCollDTO> fromFlowDTOMap = Maps.newHashMap();
        Map<String, ServiceRecordPdfOrExcelCollDTO> fromFileReportMap = Maps.newHashMap();
        for (ServiceRecordPdfOrExcelCollDTO serviceRecordPdfOrExcelCollDTO : conditionList) {
            String serviceType = serviceRecordPdfOrExcelCollDTO.getServiceType();
            if (flowTypeSet.contains(serviceType))
                fromFlowDTOMap.put(serviceRecordPdfOrExcelCollDTO.getSqfwId(), serviceRecordPdfOrExcelCollDTO);
            if (fileReportTypeSet.contains(serviceType))
                fromFileReportMap.put(serviceRecordPdfOrExcelCollDTO.getSqfwId(), serviceRecordPdfOrExcelCollDTO);
        }

        // 首先查询存储于sqfw_complete_flow中的数据
        if (fromFlowDTOMap.size() > 0) {
            Cnd cnd = Cnd.NEW();
            cnd.and("sqfw_id", "in", fromFlowDTOMap.keySet());
            cnd.orderBy("fill_date", "asc");
            List<Sqfw_formSummary> formSummaryList = query(cnd);

            Map<String, Date> completeFlowIdDateMap = formSummaryList.stream()
                    .collect(Collectors.toMap(Sqfw_formSummary::getSqfwFlowId, Sqfw_formSummary::getFillDate));

            List<String> flowIdList = formSummaryList.stream().map(Sqfw_formSummary::getSqfwFlowId).collect(Collectors.toList());

            Cnd flowCnd = Cnd.NEW();
            flowCnd.and("id", "in", flowIdList);
            List<Sqfw_completeflow> completeFlowList = sqfwCompleteflowService.query(flowCnd);
            for (Sqfw_completeflow completeFlow : completeFlowList) {
                PdfResourceSortBO bo = new PdfResourceSortBO();
                Date fillDate = completeFlowIdDateMap.get(completeFlow.getId());
                bo.setSortDate(fillDate);
                bo.setSqfwId(completeFlow.getSqfwId());
                bo.setPdfPath(completeFlow.getResourcePath());
                bo.setServiceType(completeFlow.getFileType());
                pdfBoList.add(bo);
            }
        }

        // 然后从file_report中根据服务id查询出相关的数据 这一步我们使用循环查询的方式来操作
        if (fromFileReportMap.size() > 0) {
            Set<String> sqfwIdSet = fromFileReportMap.keySet();
            for (String sqfwId : sqfwIdSet) {

                Set<String> existedReportNameSet = Sets.newHashSet();

                Cnd fileReportCnd = Cnd.NEW();
                fileReportCnd.and("sqfw_id", "=", sqfwId).orderBy("create_date", "desc");
                List<Sqfw_reportFile> reportFileList = reportFileService.query(fileReportCnd);
                for (Sqfw_reportFile reportFile : reportFileList) {
                    String reportName = reportFile.getReportName();
                    if (StringUtils.isNotBlank(reportName) && !existedReportNameSet.contains(reportName)) {
                        existedReportNameSet.add(reportName);

                        PdfResourceSortBO bo = new PdfResourceSortBO();
                        bo.setSortDate(reportFile.getCreateDate());
                        bo.setSqfwId(reportFile.getSqfwId());
                        bo.setPdfPath(reportFile.getPdfFileUrl());
                        bo.setServiceType(reportFile.getServiceRecordType());
                        pdfBoList.add(bo);
                    }
                }
            }
        }

        // 将这些文档按照时间先后顺序进行排序
        pdfBoList.sort(Comparator.comparing(PdfResourceSortBO::getSortDate));

        List<String> pdfPathList = pdfBoList.stream().map(PdfResourceSortBO::getPdfPath).collect(Collectors.toList());

        // 用于生成文件规则
        Set<String> serviceTypeTotalSet = conditionList.stream().map(ServiceRecordPdfOrExcelCollDTO::getServiceType).collect(Collectors.toSet());
        String fileNameTemplate = "{}文档集合{}";

        String fileNamePrefix = "工作文档";
        if (serviceTypeTotalSet.size() == 1) fileNamePrefix = serviceTypeTotalSet.iterator().next();
        String fileName = StrFormatter.format(fileNameTemplate, fileNamePrefix, DateUtil.format(new Date(), "yyyyMMddHHmmss"));
        return CommonFormOfficeUtils.createConvergePdfAndUpload(pdfPathList, fileName);
    }

    /**
     * 公共获取pdf和Excel下载sumList列表
     *
     * @return
     */
    public List<SqfwSummaryPdfExcelCollForAdminDTO> getPdfExcelFileDownSumIdList(String sqlStr, String type) {
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<SqfwSummaryPdfExcelCollForAdminDTO> list = Lists.newArrayList();
            while (rs.next()) {
                SqfwSummaryPdfExcelCollForAdminDTO newCond = new SqfwSummaryPdfExcelCollForAdminDTO();
                newCond.setServiceType(type);
                newCond.setSumId(rs.getString("id"));
                list.add(newCond);
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(SqfwSummaryPdfExcelCollForAdminDTO.class);
    }

    /**
     * 任务记录相关文件导出
     * 由于复用生成文件代码，且不上传存储服务器，直接返回文件路径，所以copy此方法来进行修正
     */
    public String generateServiceRecordExcelCollCopyApp(String downType, List<ServiceRecordPdfOrExcelCollDTO> conditionList, String userId, Integer fileIndex, String excelType) {
        if (conditionList.size() == 0) return null;

        Set<String> serviceTypeSet = conditionList.stream().map(ServiceRecordPdfOrExcelCollDTO::getServiceType).collect(Collectors.toSet());
        if (serviceTypeSet.size() > 1) return null;

        List<String> sqfwIdList = conditionList.stream().map(ServiceRecordPdfOrExcelCollDTO::getSqfwId).collect(Collectors.toList());

        String serviceType = conditionList.get(0).getServiceType();

        String fileNameSuffix = StrFormatter.format("集合{}", DateUtil.format(new Date(), "yyyyMMddHHmmss"));

        if (serviceType.equals("安全排查")) {
            return generateFireHiddenCollectionExcelCopyApp(downType, sqfwIdList, null, userId, fileIndex, fileNameSuffix);
        }

        if (serviceType.equals("垃圾分类")) {
            return generateRefuseClassifyExcelCopyApp(downType, sqfwIdList, fileNameSuffix);
        }

        if (serviceType.equals("入户调查")) {
            return generateHouseSurveyFormExcelCopyApp(downType, sqfwIdList, excelType, fileNameSuffix);
        }

        return null;
    }

    /**
     * 根据服务记录id生成消防安全excel数据
     *
     * @param sqfwId      服务记录id
     * @param workOrderId 工单id
     * @param userId      用户id
     * @param fileIndex   文件索引
     * @return excel文件链接
     */
    public String generateFireHiddenCollectionExcelCopyApp(String downType, String sqfwId, String workOrderId, String userId, Integer fileIndex) {
        if (StringUtils.isBlank(sqfwId)) return null;
        return generateFireHiddenCollectionExcelCopyApp(downType, Lists.newArrayList(sqfwId), workOrderId, userId, fileIndex, null);
    }

    private String generateFireHiddenCollectionExcelCopyApp(String downType, List<String> sqfwIdList, String workOrderId, String userId, Integer fileIndex, String fileNameSuffix) {
        if (sqfwIdList.size() == 0) return null;
        Cnd cnd = Cnd.NEW();
        cnd.and("sqfw_id", "in", sqfwIdList);

        if (StringUtils.isNotBlank(workOrderId)) {
            cnd.and("work_order_id", "=", workOrderId);
        }

        cnd.orderBy("fill_date", "asc");

        List<Sqfw_formSummary> formSummaryList = query(cnd);

        String sqfwId = sqfwIdList.get(0);
        Sqfw_servicerecord serviceRecord = sqfwServicerecordService.fetch(sqfwId);

        AbstractExcelParamDTO excelParamDTO = new AbstractExcelParamDTO();
        if ("zip".equals(downType)) {
            excelParamDTO.setReturnWebUrlFlag(false);
        }
        excelParamDTO.setFillDate(new Date());
        excelParamDTO.setSignalPersonImgUrl("");
        excelParamDTO.setUnitNamePrefix(serviceRecord.getCompanyName());
        excelParamDTO.setCompleteUnitName(serviceRecord.getCompanyName());

        excelParamDTO.setFormSummaryList(formSummaryList);

        String unitName = "";
        try {
            Sys_user createServiceRecordUser = sysUserService.getUserById(serviceRecord.getUser_id());
            if (StringUtils.isNotEmpty(serviceRecord.getUser_id())) {
                String unitId = createServiceRecordUser.getUnitId();
                Sys_unit unit = sysUnitService.getMasterCompany(unitId);
                unitName = unit.getName();
            }
        } catch (BaseException bex) {
            log.info("查询异常：{}", bex.getMessage());
            unitName = formSummaryList.get(0).getCounty();
        } finally {
            if (StringUtils.isEmpty(unitName)) {
                throw new BaseException("用户不存在");
            }
        }

        String titleUnitName = serviceRecord.getCounty();
        if (Strings.isBlank(titleUnitName)) {
            titleUnitName = unitName;
        }
        if (!titleUnitName.contains("街道")) {
            titleUnitName += "街道";
        }
        if (Strings.isNotBlank(titleUnitName)) {
            titleUnitName = titleUnitName.substring(0, titleUnitName.lastIndexOf("街道") + 2);
        }
        // 油榨街街道的
        if (unitName.contains("油榨街")) {
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            return FireHiddenOfficeUtils.createFireHiddenExcelYouZhaJieShiNanSQ(excelParamDTO, null);
        }

        // 金元社区的
        if (unitName.contains("金元") || unitName.contains("润兴") || unitName.contains("金阳")) {
            Sys_user createFileUser = sysUserService.fetch(userId);

            FireHiddenExcelParamJinYuanSQDTO dto = new FireHiddenExcelParamJinYuanSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);

            if (unitName.contains("金元")) {
                dto.setVisAndInsExcelTitlePrefix(StrFormatter.format("金阳街道金元社区九小场所{}", DateUtil.format(dto.getFillDate(), "yyyy年MM月")));
                dto.setGasInsExcelTitlePrefix("金阳街道金元社区");
                dto.setGasRecExcelTitlePrefix("观山湖区金元社区");
                dto.setFireSafetyExcelTitlePrefix("金阳街道金元社区");
            }

            if (unitName.contains("润兴")) {
                dto.setVisAndInsExcelTitlePrefix(StrFormatter.format("金阳街道润兴居委会{}", DateUtil.format(dto.getFillDate(), "yyyy年MM月")));
                dto.setGasInsExcelTitlePrefix("金阳街道润兴居委会");
                dto.setGasRecExcelTitlePrefix("观山湖区润兴居委会");
                dto.setFireSafetyExcelTitlePrefix("金阳街道润兴居委会");
            }

            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setCompleteUnitName(titleUnitName);

            if (fileIndex == null) fileIndex = 1;
            dto.setFileIndex(fileIndex);

            // 如果是生成台账，则需要查询一些数据用来进行填装
            if (fileIndex == 0 || fileIndex == 4) {
                List<Sqfw_formSummary> jinYuanFormSummaryList = dto.getFormSummaryList();

                Map<String, Sqfw_formSummary> latestA1AndA13Map = Maps.newHashMap();
                for (Sqfw_formSummary jinYuanFormSummary : jinYuanFormSummaryList) {
                    String jinYuanA1 = jinYuanFormSummary.getA1();
                    String jinYuanA13 = jinYuanFormSummary.getA13();

                    if (jinYuanA13.equals("整改完成")) {
                        Cnd jinYuanCnd = Cnd.NEW();
                        jinYuanCnd.and("a1", "=", jinYuanA1).and("a13", "=", "有");
                        jinYuanCnd.orderBy("fill_date", "desc");
                        Sqfw_formSummary jinYuanQueryFormSummary = fetch(jinYuanCnd);
                        if (jinYuanQueryFormSummary != null && !jinYuanQueryFormSummary.getId().equals(jinYuanFormSummary.getId()))
                            latestA1AndA13Map.put(jinYuanQueryFormSummary.getA1(), jinYuanQueryFormSummary);
                    }

                }
                dto.setLatestA1AndA13Map(latestA1AndA13Map);
            }

            // 生成文件
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            VisAndInsForJinYangJieDaoExcelVO jinYuanFireHiddenExcelVO = FireHiddenOfficeUtils.createFireHiddenExcelJinYuanSQ(dto, null);
            if (jinYuanFireHiddenExcelVO.getErrorStatus()) return null;

            if (fileIndex == 1) return jinYuanFireHiddenExcelVO.getVisAndInsExcelUrl();
            if (fileIndex == 2) return jinYuanFireHiddenExcelVO.getGasInsExcelUrl();
            if (fileIndex == 3) return jinYuanFireHiddenExcelVO.getGasRecExcelUrl();
            if (fileIndex == 4) return jinYuanFireHiddenExcelVO.getStandingBookExcelUrl();

            if (fileIndex == 0)
                return StrFormatter.format("{},{},{},{}", jinYuanFireHiddenExcelVO.getVisAndInsExcelUrl(),
                        jinYuanFireHiddenExcelVO.getGasInsExcelUrl(), jinYuanFireHiddenExcelVO.getGasRecExcelUrl(), jinYuanFireHiddenExcelVO.getStandingBookExcelUrl());
        }

        //大营路街道
        if (unitName.contains("大营路")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamDaYingLuSQDTO dto = new FireHiddenExcelParamDaYingLuSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setStreet(createFileUser.getCounty());
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            dto.setArea(serviceRecord.getDistrict());
            dto.setStreet(serviceRecord.getCounty());
            dto.setDataSource(CommonFormOfficeForAdminUtils.APPLICATION_ADMIN);

            List<Sqfw_formSummary> preFormSummaryList = dto.getFormSummaryList();
            Map<String, Sqfw_formSummary> lastFormSummaryCheckMap = Maps.newHashMap();
            for (Sqfw_formSummary formSummary : preFormSummaryList) {
                Cnd dylLastCheckCND = Cnd.NEW();
                dylLastCheckCND.and(Sqfw_formSummary::getA1, "=", formSummary.getA1())
                        .and(Sqfw_formSummary::getUnitId, "=", formSummary.getUnitId());
                dylLastCheckCND.orderBy(Sqfw_formSummary::getFillDate, "desc");
                Sqfw_formSummary lastCheckFormSummary = fetch(dylLastCheckCND);
                if (lastCheckFormSummary != null && !lastCheckFormSummary.getId().equals(formSummary.getId())) {
                    lastFormSummaryCheckMap.put(formSummary.getId(), lastCheckFormSummary);
                }
            }
            dto.setLastFormSummaryCheckMap(lastFormSummaryCheckMap);
            return DaYingLuStreetFireHiddenExcelUtils.createFireHiddenExcelDaYingLuSQ(dto, null);
        }

        //威清门街道
        if (unitName.contains("威清门")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamWeiQingMenSQDTO dto = new FireHiddenExcelParamWeiQingMenSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setProvince(createFileUser.getProvince());
            dto.setCity(createFileUser.getCity());
            dto.setDistrict(createFileUser.getDistrict());
            dto.setCounty(createFileUser.getCounty());
            //dto.setCommunity(createFileUser.getCommunity());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            return WeiQingMenStreetFireHiddenExcelUtils.createFireHiddenExcelSQ(dto, null);
        }

        //宇虹社区
        if (unitName.contains("云潭街道")) {
            Sys_user createFileUser = sysUserService.fetch(userId);
            FireHiddenExcelParamYuHongSQDTO dto = new FireHiddenExcelParamYuHongSQDTO();
            BeanUtil.copyProperties(excelParamDTO, dto);
            dto.setCreateUserName(createFileUser.getUsername());
            dto.setCreateUserPhone(createFileUser.getLoginname());
            dto.setCounty(createFileUser.getCounty());
            //dto.setCommunity(createFileUser.getCommunity());
            dto.setFileIndex(fileIndex);
            dto.setCompleteUnitName(titleUnitName);
            return YuHongMenStreetFireHiddenExcelUtils.createExcelSQ(dto, null);
        }

        // 其他社区的
        return FireHiddenOfficeUtils.createFireHiddenExcel(excelParamDTO, null);

    }

    public String generateRefuseClassifyExcelCopyApp(String downType, String sqfwId) {
        if (StringUtils.isBlank(sqfwId)) return null;
        return generateRefuseClassifyExcelCopyApp(downType, Lists.newArrayList(sqfwId), null);
    }

    private String generateRefuseClassifyExcelCopyApp(String downType, List<String> sqfwIdList, String fileNameSuffix) {
        if (sqfwIdList.size() == 0) return null;
        Cnd cnd = Cnd.NEW();
        cnd.and("sqfw_id", "in", sqfwIdList).and("type", "=", SqfwFormSummaryConstants.refuse_classify);
        cnd.orderBy("fill_date", "asc");
        List<Sqfw_formSummary> formSummaryList = query(cnd);

        String sqfwId = sqfwIdList.get(0);
        Sqfw_servicerecord serviceRecord = sqfwServicerecordService.fetch(sqfwId);

        RefuseClassifyExcelParamDTO excelParamDTO = new RefuseClassifyExcelParamDTO();
        if ("zip".equals(downType)) {
            excelParamDTO.setReturnWebUrlFlag(false);
        }
        excelParamDTO.setFillDate(new Date());
        excelParamDTO.setSignalPersonImgUrl("");
        excelParamDTO.setAreaLevel(serviceRecord.getCounty() + serviceRecord.getCommunity());
        excelParamDTO.setCompleteUnitName(serviceRecord.getCompanyName());

        excelParamDTO.setFormSummaryList(formSummaryList);
        try {
            excelParamDTO.setFileNameSuffix(fileNameSuffix);
            return RefuseClassifyOfficeUtils.createRefuseClassifyExcel(excelParamDTO, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 按照类型生成入户调查表excel
     *
     * @param sqfwId    社区服务id
     * @param excelType excel类型
     * @return excel链接
     */
    public String generateHouseSurveyFormExcelCopyApp(String downType, String sqfwId, String excelType) {
        if (StringUtils.isBlank(sqfwId)) {
            throw new AppException("业务异常[请求参数不能为空]");
        }
        return generateHouseSurveyFormExcelCopyApp(downType, Lists.newArrayList(sqfwId), excelType, null);
    }

    private String generateHouseSurveyFormExcelCopyApp(String downType, List<String> sqfwIdList, String excelType, String fileNameSuffix) {

        // wzy 2023-10-11 临时处理垃圾分类改为垃圾分类宣传
    /*    String tempExcelType = excelType;
        if (tempExcelType.equals("垃圾分类")) tempExcelType = "垃圾分类宣传";*/

        if (sqfwIdList.size() == 0) return null;
        Cnd cnd = Cnd.NEW();
        cnd.and("sqfw_id", "in", sqfwIdList).and("type", "=", SqfwFormSummaryConstants.house_survey_form);
        cnd.orderBy("fill_date", "asc");
        List<Sqfw_formSummary> formSummaryList = query(cnd);

        return CommonComponent.generateHouseSurveyFormExcelForApp(downType, formSummaryList, excelType, fileNameSuffix, null, null, null);
    }

    @Override
    public String generateAppointPdfConverge(String userId, List<String> summaryIdList, String fileType, int index) {
        if (summaryIdList.size() == 0) return null;

        Sys_user user = sysUserService.getUserById(userId);
        user.setUnitName(StreetEnum.WEI_QING_MEN_STREET.getName());
        String path = GeneratePdfConverge.downPdfConverge(user.getUnitName(), summaryIdList, index);
        if (null == path) {
            return ResultCode.NULL_DATA_ERROR.getMsg();
        }
        return path;
    }

    @Override
    public Result<?> listSummaryWQMForBM(Sqfw_formSummaryForAdminVO bean, int pageNo, int pageSize, String pageOrderName, String pageOrderBy) {
        bean.setPageNo(pageNo);
        bean.setPageSize(pageSize);
        bean.setPageOrderName(pageOrderName);
        bean.setPageOrderBy(pageOrderBy);
        Cnd cnd = buildWQMQueryParam(bean);

        Pagination pagination = sqfwSummaryForAdminService.listPage(pageNo, pageSize, cnd);
        if (Strings.isBlank(bean.getCheckStatus()) || SqfwSummaryForAdminServiceImpl.CHECK_OUT.equals(bean.getCheckStatus())) {
            List<Sqfw_formSummary> sqfwFormSummaryList = pagination.getList(Sqfw_formSummary.class);
            List<Sqfw_formSummaryForAdminVO> sqfwFormSummaryForAdminVOList = new ArrayList<Sqfw_formSummaryForAdminVO>();
            for (Sqfw_formSummary sqfwFormSummary : sqfwFormSummaryList) {
                Sqfw_formSummaryForAdminVO sqfwFormSummaryForAdminVO = new Sqfw_formSummaryForAdminVO();
                BeanUtil.copyProperties(sqfwFormSummary, sqfwFormSummaryForAdminVO);
                if (Strings.isNotBlank(sqfwFormSummary.getCheckStatus())
                        && SqfwSummaryForAdminServiceImpl.TO_DO_CHECK_OUT.equals(sqfwFormSummary.getCheckStatus().trim())
                        && null != sqfwFormSummaryForAdminVO.getFillDate()) {
                    Date offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
                    Date thisFillDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(sqfwFormSummaryForAdminVO.getFillDate()));
                    if (offsetDate.compareTo(thisFillDate) < 0) {
                        sqfwFormSummaryForAdminVO.setCheckStatus(SqfwSummaryForAdminServiceImpl.CHECK_OUT);
                    }
                }
                sqfwFormSummaryForAdminVOList.add(sqfwFormSummaryForAdminVO);
            }
            pagination.setList(sqfwFormSummaryForAdminVOList);
        }
        return Result.success(pagination);
    }

    /**
     * 构建查询参数
     *
     * @param bean
     * @return
     */
    @Override
    public Cnd buildWQMQueryParam(Sqfw_formSummaryForAdminVO bean) {
        String pageOrderName = bean.getPageOrderName();
        String pageOrderBy = bean.getPageOrderBy();
        Cnd cnd = Cnd.NEW();
        if (Strings.isNotBlank(pageOrderName) && Strings.isNotBlank(pageOrderBy)) {
            cnd.orderBy(pageOrderName, PageUtil.getOrder(pageOrderBy));
        } else {
            cnd.orderBy("fill_date", "desc");
        }
        if (Strings.isNotBlank(bean.getQueryAll())) {
            String queryAll = bean.getQueryAll();
            SqlExpressionGroup e1 = Cnd.exps("a1", "like", "%" + queryAll + "%");
            SqlExpressionGroup e2 = Cnd.exps("a3", "like", "%" + queryAll + "%");
            SqlExpressionGroup e3 = Cnd.exps("a5", "like", "%" + queryAll + "%");
            SqlExpressionGroup e4 = Cnd.exps("a6", "like", "%" + queryAll + "%");
            SqlExpressionGroup e5 = Cnd.exps("a7", "like", "%" + queryAll + "%");
            SqlExpressionGroup e6 = Cnd.exps("a17", "like", "%" + queryAll + "%");
            SqlExpressionGroup e7 = Cnd.exps("create_user_name", "like", "%" + queryAll + "%");
            SqlExpressionGroup e8 = Cnd.exps("community", "like", "%" + queryAll + "%");
            SqlExpressionGroup e9 = Cnd.exps("a61", "like", "%" + queryAll + "%");
            cnd.and(e1.or(e2).or(e3).or(e4).or(e5).or(e6).or(e7).or(e8).or(e9));
        }
        if (Strings.isNotBlank(bean.getA1())) {
            cnd.and("a1", "like", "%" + bean.getA1() + "%");
        }
        if (Strings.isNotBlank(bean.getA2())) {
            cnd.and("a2", "like", "%" + bean.getA2() + "%");
        }
        if (Strings.isNotBlank(bean.getA3())) {
            cnd.and("a3", "like", "%" + bean.getA3() + "%");
        }
        if (Strings.isNotBlank(bean.getA5())) {
            cnd.and("a5", "like", "%" + bean.getA5() + "%");
        }
        if (StringUtils.isNotBlank(bean.getIsNightMarket())) {
            if ("是".equals(bean.getIsNightMarket())) {
                cnd.and("a6", "like", "%临摊、夜市%");
            } else if ("否".equals(bean.getIsNightMarket())) {
                cnd.and("a6", "not like", "%临摊、夜市%");
            }
        }
        if (Strings.isNotBlank(bean.getA6())) {
            cnd.and("a6", "like", "%" + bean.getA6() + "%");
        }
        if (Strings.isNotBlank(bean.getA7())) {
            cnd.and("a7", "like", "%" + bean.getA7() + "%");
        }
        if (Strings.isNotBlank(bean.getA13())) {
            cnd.and("a13", "like", "%" + bean.getA13() + "%");
        }
        if (Strings.isNotBlank(bean.getA16())) {
            cnd.and("a16", "like", "%" + bean.getA16() + "%");
        }
        if (Strings.isNotBlank(bean.getA17())) {
            cnd.and("a17", "like", "%" + bean.getA17() + "%");
        }
        if (Strings.isNotBlank(bean.getProvince())) {
            cnd.and("province", "like", "%" + bean.getProvince() + "%");
        }
        if (Strings.isNotBlank(bean.getCity())) {
            cnd.and("city", "like", "%" + bean.getCity() + "%");
        }
        if (Strings.isNotBlank(bean.getCounty())) {
            cnd.and("county", "like", "%" + bean.getCounty() + "%");
        }
        if (Strings.isNotBlank(bean.getDistrict())) {
            cnd.and("district", "like", "%" + bean.getDistrict() + "%");
        }
        if (Strings.isNotBlank(bean.getCommunity())) {
            cnd.and("community", "like", "%" + bean.getCommunity() + "%");
        }
        if (Strings.isNotBlank(bean.getCreateUserName())) {
            cnd.and("create_user_name", "like", "%" + bean.getCreateUserName() + "%");
        }
        if (Strings.isNotBlank(bean.getCreateUserPhone())) {
            cnd.and("create_user_phone", "like", "%" + bean.getCreateUserPhone() + "%");
        }
        if (Strings.isNotBlank(bean.getType())) {
            cnd.and("type", "like", "%" + bean.getType() + "%");
        }
        //数据库中存在这种"0001-01-01 00:00:00.000" 历史数据， 需要过滤掉
        cnd.and("fill_date", ">", DateUtil.parse("0001-01-01 00:00:00.000"));

        if (bean.getQueryTime() != null && Strings.isNotBlank(bean.getQueryTime())) {
            String time[] = bean.getQueryTime().split(",");
            cnd.and("fill_date", "between", new Object[]{new Date(Long.valueOf(time[0])), new Date(Long.valueOf(time[1]))});
        }

        // 数据统计页面 隐患数据跳转过来查询列表使用 add by LJT 2023-11-16
        if (Strings.isNotBlank(bean.getIssueStatus()) && "statistic".equals(bean.getIssueStatus())) {
            cnd.andNot("issue_status", "is", null);
            cnd.and("issue_status", "!=", "正常");
        }
        // 非数据统计页面 是否存在隐患 add by LJT 2023-12-06
        if (Strings.isNotBlank(bean.getIssueStatus()) && !"statistic".equals(bean.getIssueStatus())) {
            if ("是".equals(bean.getIssueStatus())) {
                cnd.andNot("issue_status", "is", null);
                cnd.and("issue_status", "!=", "正常");
            } else if ("否".equals(bean.getIssueStatus())) {
                cnd.and("issue_status", "=", "正常");
            }
        }

        // 增加巡查状态 add by LJT 2023-11-22
        if (Strings.isNotBlank(bean.getCheckStatus()) && SqfwSummaryForAdminServiceImpl.CHECK_OUT.equals(bean.getCheckStatus())) {
            Date offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            cnd.and("fill_date", ">=", offsetDate);
            cnd.and("check_status", "!=", SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
        }

        // 如果是查询未超时数据，只查询7天前的数据（需求）
        if (Strings.isNotBlank(bean.getCheckStatus()) && SqfwSummaryForAdminServiceImpl.TO_DO_CHECK_OUT.equals(bean.getCheckStatus())) {
            cnd.and("check_status", "=", bean.getCheckStatus());
            Date offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            cnd.and("fill_date", "<", offsetDate);
            cnd.and("check_status", "!=", SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
        }
        if (Strings.isNotBlank(bean.getCheckStatus()) && SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(bean.getCheckStatus())) {
            cnd.and("check_status", "=", bean.getCheckStatus());
        }

        // 需求增加a61查询条件
        if (Strings.isNotBlank(bean.getA61())) {
            cnd.and("a61", "like", "%" + bean.getA61() + "%");
        }

        return cnd;
    }

    @Override
    public Result<?> listSummaryDYLForBM(Sqfw_formSummaryForAdminVO bean, int pageNo, int pageSize, String pageOrderName, String pageOrderBy) {
        bean.setPageNo(pageNo);
        bean.setPageSize(pageSize);
        bean.setPageOrderName(pageOrderName);
        bean.setPageOrderBy(pageOrderBy);
        Cnd cnd = buildDYLQueryParam(bean);

        Pagination pagination = sqfwSummaryForAdminService.listPage(pageNo, pageSize, cnd);
        if (Strings.isBlank(bean.getCheckStatus()) || SqfwSummaryForAdminServiceImpl.CHECK_OUT.equals(bean.getCheckStatus())) {
            List<Sqfw_formSummary> sqfwFormSummaryList = pagination.getList(Sqfw_formSummary.class);
            List<Sqfw_formSummaryForAdminVO> sqfwFormSummaryForAdminVOList = new ArrayList<Sqfw_formSummaryForAdminVO>();
            for (Sqfw_formSummary sqfwFormSummary : sqfwFormSummaryList) {
                Sqfw_formSummaryForAdminVO sqfwFormSummaryForAdminVO = new Sqfw_formSummaryForAdminVO();
                BeanUtil.copyProperties(sqfwFormSummary, sqfwFormSummaryForAdminVO);
                if (Strings.isNotBlank(sqfwFormSummary.getCheckStatus())
                        && SqfwSummaryForAdminServiceImpl.TO_DO_CHECK_OUT.equals(sqfwFormSummary.getCheckStatus().trim())
                        && null != sqfwFormSummaryForAdminVO.getFillDate()) {
                    Date offsetDate = new Date();
                    if ("安全排查".equals(bean.getType())) {
                        offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
                    } else if ("社会治安".equals(bean.getType())) {
                        offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.SDF_START.format(DateUtil.beginOfWeek(new Date())));
                    }
                    Date thisFillDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(sqfwFormSummaryForAdminVO.getFillDate()));
                    if (offsetDate.compareTo(thisFillDate) < 0) {
                        sqfwFormSummaryForAdminVO.setCheckStatus(SqfwSummaryForAdminServiceImpl.CHECK_OUT);
                    }
                }
                sqfwFormSummaryForAdminVOList.add(sqfwFormSummaryForAdminVO);
            }
            pagination.setList(sqfwFormSummaryForAdminVOList);
        }
        return Result.success(pagination);
    }

    @Override
    public Cnd buildDYLQueryParam(Sqfw_formSummaryForAdminVO bean) {
        String pageOrderName = bean.getPageOrderName();
        String pageOrderBy = bean.getPageOrderBy();
        Cnd cnd = Cnd.NEW();
        if (Strings.isNotBlank(pageOrderName) && Strings.isNotBlank(pageOrderBy)) {
            cnd.orderBy(pageOrderName, PageUtil.getOrder(pageOrderBy));
        } else {
            cnd.orderBy("fill_date", "desc");
        }
        if (Strings.isNotBlank(bean.getQueryAll())) {
            String queryAll = bean.getQueryAll();
            SqlExpressionGroup e1 = Cnd.exps("a1", "like", "%" + queryAll + "%");
            SqlExpressionGroup e2 = Cnd.exps("a2", "like", "%" + queryAll + "%");
            SqlExpressionGroup e3 = Cnd.exps("a5", "like", "%" + queryAll + "%");
            SqlExpressionGroup e4 = Cnd.exps("a7", "like", "%" + queryAll + "%");
            SqlExpressionGroup e5 = Cnd.exps("a8", "like", "%" + queryAll + "%");
            SqlExpressionGroup e6 = Cnd.exps("a13", "like", "%" + queryAll + "%");
            SqlExpressionGroup e7 = Cnd.exps("a16", "like", "%" + queryAll + "%");
            SqlExpressionGroup e8 = Cnd.exps("community", "like", "%" + queryAll + "%");
            SqlExpressionGroup e9 = Cnd.exps("create_user_name", "like", "%" + queryAll + "%");
            cnd.and(e1.or(e2).or(e3).or(e4).or(e5).or(e6).or(e7).or(e8).or(e9));
        }

        if (Strings.isNotBlank(bean.getA1())) {
            cnd.and("a1", "like", "%" + bean.getA1() + "%");
        }
        if (Strings.isNotBlank(bean.getA2())) {
            cnd.and("a2", "like", "%" + bean.getA2() + "%");
        }
        if (Strings.isNotBlank(bean.getA3())) {
            cnd.and("a3", "like", "%" + bean.getA3() + "%");
        }
        if (Strings.isNotBlank(bean.getA5())) {
            cnd.and("a5", "like", "%" + bean.getA5() + "%");
        }
        if (Strings.isNotBlank(bean.getA6())) {
            cnd.and("a6", "like", "%" + bean.getA6() + "%");
        }
        if (Strings.isNotBlank(bean.getA7())) {
            cnd.and("a7", "like", "%" + bean.getA7() + "%");
        }
        if (Strings.isNotBlank(bean.getA13())) {
            cnd.and("a13", "like", "%" + bean.getA13() + "%");
        }
        if (Strings.isNotBlank(bean.getA16())) {
            cnd.and("a16", "like", "%" + bean.getA16() + "%");
        }
        if (Strings.isNotBlank(bean.getA17())) {
            cnd.and("a17", "like", "%" + bean.getA17() + "%");
        }
        if (Strings.isNotBlank(bean.getA33())) {
            if ("没有住人".equals(bean.getA33())) {
                SqlExpressionGroup e1 = Cnd.exps("a33", "=", "");
                SqlExpressionGroup e2 = Cnd.exps("a33", "=", null);
                cnd.and(e1.or(e2));
            } else if ("住人问题已整改".equals(bean.getA33())) {
                cnd.and("a33", "=", "没有住人");
            } else {
                cnd.and("a33", "like", "%" + bean.getA33() + "%");
            }
        }
        if (Strings.isNotBlank(bean.getProvince())) {
            cnd.and("province", "=", bean.getProvince());
        }
        if (Strings.isNotBlank(bean.getCity())) {
            cnd.and("city", "=", bean.getCity());
        }
        if (Strings.isNotBlank(bean.getCounty())) {
            cnd.and("county", "=", bean.getCounty());
        }
        if (Strings.isNotBlank(bean.getDistrict())) {
            cnd.and("district", "=", bean.getDistrict());
        }
        if (Strings.isNotBlank(bean.getCommunity())) {
            cnd.and("community", "=", bean.getCommunity());
        }
        if (Strings.isNotBlank(bean.getCreateUserName())) {
            cnd.and("create_user_name", "like", "%" + bean.getCreateUserName() + "%");
        }
        if (Strings.isNotBlank(bean.getCreateUserPhone())) {
            cnd.and("create_user_phone", "like", "%" + bean.getCreateUserPhone() + "%");
        }
        if (Strings.isNotBlank(bean.getType())) {
            String types[] = bean.getType().split("\\,");
            cnd.and("type", "in", types);
        }
        //数据库中存在这种"0001-01-01 00:00:00.000" 历史数据， 需要过滤掉
        cnd.and("fill_date", ">", DateUtil.parse("0001-01-01 00:00:00.000"));

        if (bean.getQueryTime() != null && Strings.isNotBlank(bean.getQueryTime())) {
            String time[] = bean.getQueryTime().split(",");
            cnd.and("fill_date", "between", new Object[]{new Date(Long.valueOf(time[0])), new Date(Long.valueOf(time[1]))});
        }

        // 数据统计页面 隐患数据跳转过来查询列表使用 add by LJT 2023-11-16
        if (Strings.isNotBlank(bean.getIssueStatus()) && "statistic".equals(bean.getIssueStatus())) {
            cnd.andNot("issue_status", "is", null);
            cnd.and("issue_status", "!=", "正常");
        }

        // 非数据统计页面 是否存在隐患 add by LJT 2023-12-06
        if (Strings.isNotBlank(bean.getIssueStatus()) && !"statistic".equals(bean.getIssueStatus())) {
            if ("是".equals(bean.getIssueStatus())) {
                cnd.andNot("issue_status", "is", null);
                cnd.and("issue_status", "!=", "正常");
            } else if ("否".equals(bean.getIssueStatus())) {
                cnd.and("issue_status", "=", "正常");
            }
        }

        // 增加巡查状态 add by LJT 2023-11-22
        if (Strings.isNotBlank(bean.getCheckStatus()) && SqfwSummaryForAdminServiceImpl.CHECK_OUT.equals(bean.getCheckStatus())) {
            Date offsetDate = new Date();
            if ("安全排查".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            } else if ("社会治安".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.SDF_START.format(DateUtil.beginOfWeek(new Date())));
            }
            SqlExpressionGroup e1 = Cnd.exps("fill_date", ">=", offsetDate);
            SqlExpressionGroup e2 = Cnd.exps("check_status", "!=", SqfwSummaryForAdminServiceImpl.TO_DO_CHECK_OUT);
            cnd.and(e1.or(e2));
            cnd.and("check_status", "!=", SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
        }
        if (Strings.isNotBlank(bean.getCheckStatus()) && SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(bean.getCheckStatus())) {
            cnd.and("check_status", "=", bean.getCheckStatus());
        }

        // 如果是查询未超时数据，只查询7天前的数据（需求）
        if (Strings.isNotBlank(bean.getCheckStatus()) && SqfwSummaryForAdminServiceImpl.TO_DO_CHECK_OUT.equals(bean.getCheckStatus())) {
            cnd.and("check_status", "=", bean.getCheckStatus());
            Date offsetDate = new Date();
            if ("安全排查".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.DATE_FORMAT.format(DateUtil.offsetDay(new Date(), -CommonFormOfficeForAdminUtils.TIME_OUT)));
            } else if ("社会治安".equals(bean.getType())) {
                offsetDate = DateUtil.parse(CommonFormOfficeForAdminUtils.SDF_START.format(DateUtil.beginOfWeek(new Date())));
            }
            cnd.and("check_status", "!=", SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
            cnd.and("fill_date", "<", offsetDate);
        }

        // 产品要求增加测试数据过滤,街道包含测试的账号才展示测试数据
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (ObjectUtil.isNotEmpty(sysUser) && StringUtils.isNotEmpty(sysUser.getCounty()) && !"直属".equals(sysUser.getCounty())) {
            if (sysUser.getCounty().contains("测试")) {
                cnd.and("county", "like", "%测试%");
            } else {
                cnd.andNot("county", "like", "%测试%");
            }
        }

        // 需求：增加a68涉黑涉恶字段查询
        if (Strings.isNotBlank(bean.getA68())) {
            if ("0".equals(bean.getA68())) {
                SqlExpressionGroup e1 = Cnd.exps("a68", "=", "无");
                SqlExpressionGroup e2 = Cnd.exps("a68", "=", null);
                cnd.and(e1.or(e2));
            } else if ("1".equals(bean.getA68())) {
                cnd.and("a68", "!=", "无");
                cnd.andNot("a68", "is", null);
            }
        }
        return cnd;
    }

    @Override
    @Aop(TransAop.READ_COMMITTED)
    public void updateSummaryInfo(Sqfw_formSummaryForAdminVO sqfwFormSummaryVO) {
        if (StringUtils.isEmpty(sqfwFormSummaryVO.getId())) {
            throw new AppException("id不能为空");
        }
        Sqfw_formSummary sqfwFormSummary = fetch(sqfwFormSummaryVO.getId());
        if (sqfwFormSummary == null) {
            throw new AppException(ResultCode.NULL_DATA_ERROR.getMsg());
        }
        // 复制对象
        Sqfw_formSummary sqfwFormSummary1 = new Sqfw_formSummary();
        BeanUtil.copyProperties(sqfwFormSummaryVO, sqfwFormSummary1);
        sqfwFormSummary1.setUpdateBy(SecurityUtil.getUserId()).setUpdateDate(new Date());
        this.updateIgnoreNull(sqfwFormSummary1);
    }
}
